MySQL Null Values
This is a real table rest. Let's say I have this code:
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`;
I get
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 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 | | |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
Why power col
12 and not 7? There are 7 unique values, so why are all NULLs counted individually? Does the size of the index increase? As soon as I use empty strings instead of NULL values, the cardinality decreases. Which value is preferred?
source to share
From MySQL documentation here
The cardinality value is calculated based on statistics stored as integers, so the value is not necessarily accurate even for small tables
This means that NULL values aren't stored as duplicates in the column, which makes sense. NULL values arent known. Hence, no two NULLs are equal.
Refer here
Edit: this is why you cannot compare NULL values in SQL with =
, you should always useis NULL
Conclusion: Normality 12 is correct.
Edit: I forgot to answer your other questions.
Does the size of the index increase? Answer in MySQL documentation
The UNIQUE index creates a constraint so that all values in the index must be different. An error occured if you try to add a new row with a key value that matches an existing row. This limitation does not apply to null values, except for the BDB storage engine. For other engines, the UNIQUE index allows multiple NULL values for columns that can contain NULLs. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique in the prefix.
As soon as I use empty strings instead of NULL values, the cardinality decreases. What is the preferred value? There is no preferred value. If the blank line works for your purpose then use them. The cardinality is reduced because empty string = empty string is correct, but NULL = NULL is not
source to share