这是对真实表的重新创建.说我有这段代码：

CREATE TABLE `testTable` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`col` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `testTable` (col) VALUES (NULL), ('a'), (NULL), ('b'), (NULL), ('c'), (NULL), ('d'), (NULL), ('e'), (NULL), ('f');
ALTER TABLE `testTable` ADD INDEX (`col`);
OPTIMIZE TABLE `testTable`;
SHOW INDEX FROM `testTable`;

我懂了

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| testTable |          0 | PRIMARY  |            1 | id          | A         |          12 |     NULL | NULL   |      | BTREE      |         |               |
| testTable |          1 | col      |            1 | col         | A         |          12 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

为什么第12列而不是7的基数？有7个唯一值,那么为什么要分别计算所有NULL？这会增加索引的大小吗？一旦我使用空字符串而不是NULL值,基数就会下降.哪个是首选值？

解决方法:

从MySQL文档here

Cardinality is counted based on statistics stored as integers, so the value is not necessarily exact even for small tables

这意味着将NULL值作为重复存储在列中是有意义的.空值arent已知的值.因此,没有两个NULL相等.

参考here

编辑：这就是为什么您不能将SQL中的NULL值与=进行比较,所以您始终必须使用NULL

结论：基数12是正确的.

编辑：我忘记回答您的其他问题.

这会增加索引的大小吗？答案在MySQL文档中

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.

一旦我使用空字符串而不是NULL值,基数就会下降.哪个是首选值？照此没有首选值.如果空字符串适合您的目的,请使用它们.基数下降,因为空字符串=空字符串是正确的,但NULL = NULL不是