1

In the following SQL, Multi-Valued Indexes are generated for the json array.

ALTER TABLE table_name ADD INDEX index_name((CAST(json->'$.field' AS json_type ARRAY))).

ALTER TABLE table_name ADD INDEX index_name((CAST(json->'$.field' AS UNSIGNED ARRAY)));
ALTER TABLE table_name ADD INDEX index_name((CAST(json->'$.field' AS CHAR(255) ARRAY)));

type can be defined to BINARY[(N)] - CHAR[(N)] [charset_info] - DATE - DATETIME - TIME - DECIMAL[(M[,D])] - SIGNED [INTEGER] - UNSIGNED [INTEGER].

Why is the type char(n) instead of varchar(n)?

Is the char(n) here the same as the MySQL type? Will spaces be filled in as well?

feilong gao
  • 31
  • 2

1 Answers1

0

Using CHAR over VARCHAR makes all the sense in the world. Why ???

Please note you get better performance reading from and writing to a CHAR rather than a VARCHAR. The tradeoff would be the use of more disk/memory.

While using VARCHAR would use less disk/memory, it would be slower because of the CPU churn needed to calculate and store string lengths in addition to read/write performance on such columns. Such would not be the case with a CHAR field.

I have mentioned CHAR performance over VARCHAR before

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • Is each element in the char(n) array equivalent to char?Are spaces filled when length is not enough? – feilong gao Jan 03 '24 at 02:40
  • There will usually be some CPU load to calculate string lengths anyway. CHAR stores a fixed number of characters, but it might still be a variable length in bytes, because modern applications typically use UTF-8. The performance gain may only apply to fixed-width character sets, and only to MyISAM. – Bill Karwin Jan 03 '24 at 04:10
  • @BillKarwin Is each element in the char(n) array equivalent to char or varchar?I guess varchar,because char use more space in spaces.But i haven't find any doc in here. – feilong gao Jan 03 '24 at 06:57
  • @feilonggao Read https://dev.mysql.com/doc/refman/8.2/en/innodb-row-format.html and search for paragraphs that mention CHAR. – Bill Karwin Jan 03 '24 at 07:07
  • I know char in there.What I am referring to here is char array index.Is each element stored in char format?why there is no varchar array index? – feilong gao Jan 04 '24 at 05:51