mysql字符串数据类型_MySQL数据类型--------字符串类型实战

1. 背景

* MySQL支持的字符串类型有CHAR、VARCHAR、二进制、var二进制、BLOB、文本等等。

2. 字符串类型所需的存储和值范围类型说明N的含义是否有字符集最大长度

CHAR(N)定义字符字符是255

VARCHAR(N)变长字符字符是16384

BINARY(N)定长二进制字节字节否255

VARBINARY(N)变长二进制字节字节否16384

TINYBLOB二进制大对象字节否256

BLOB二进制大对象字节否16K

MEDIUMBLOB二进制大对象字节否16M

LONGBLOB二进制大对象字节否4G

TINYTEXT大对象字节是256

TEXT大对象字节是16K

MEDUIMBLOB大对象字节是16M

LONGTEXT大对象字节是4G

3. 定义与变长区别 (CHAR VS VARCHAR)值CHAR(4)占用空间VARHCAR(4)占用空间

'''    '4 bytes''1 bytes

'ab''ab  '4 bytes'ab'3 bytes

'abcd''abcd'4 bytes'abcd'5 bytes

'abcdefgh''abcd'4 bytes'abcd'5 bytes

4. 字符串类型相关注意事项

* 在BLOB和TEXT列上创建索引时，必须制定索引前缀的长度

* VARCHAR和VARBINARY必须长度是可选的

* BLOB和TEXT列不能有默认值

* BLOB和TEXT列排序时只使用该列的前max_sort_length个字节mysql> show variables like 'max_sort_length';

+-----------------+-------+

| Variable_name   | Value |

+-----------------+-------+

| max_sort_length | 1024  |

+-----------------+-------+

1 row in set (0.01 sec)

5. 字符串类型与排序规则

* 查看字符串相关信息　[ Default collation: 默认排序规则 ]mysql> show character set like 'utf8mb4';

+---------+---------------+--------------------+--------+

| Charset | Description   | Default collation  | Maxlen |

+---------+---------------+--------------------+--------+

| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci |      4 |

+---------+---------------+--------------------+--------+

1 row in set (0.01 sec)

* 查看utfmb4所有排序规则mysql> show collation like 'utf8mb4%';

+------------------------+---------+-----+---------+----------+---------+

| Collation              | Charset | Id  | Default | Compiled | Sortlen |

+------------------------+---------+-----+---------+----------+---------+

| utf8mb4_general_ci     | utf8mb4 |  45 | Yes     | Yes      |       1 |

| utf8mb4_bin            | utf8mb4 |  46 |         | Yes      |       1 |

| utf8mb4_unicode_ci     | utf8mb4 | 224 |         | Yes      |       8 |

| utf8mb4_icelandic_ci   | utf8mb4 | 225 |         | Yes      |       8 |

| utf8mb4_latvian_ci     | utf8mb4 | 226 |         | Yes      |       8 |

| utf8mb4_romanian_ci    | utf8mb4 | 227 |         | Yes      |       8 |

| utf8mb4_slovenian_ci   | utf8mb4 | 228 |         | Yes      |       8 |

| utf8mb4_polish_ci      | utf8mb4 | 229 |         | Yes      |       8 |

| utf8mb4_estonian_ci    | utf8mb4 | 230 |         | Yes      |       8 |

| utf8mb4_spanish_ci     | utf8mb4 | 231 |         | Yes      |       8 |

| utf8mb4_swedish_ci     | utf8mb4 | 232 |         | Yes      |       8 |

| utf8mb4_turkish_ci     | utf8mb4 | 233 |         | Yes      |       8 |

| utf8mb4_czech_ci       | utf8mb4 | 234 |         | Yes      |       8 |

| utf8mb4_danish_ci      | utf8mb4 | 235 |         | Yes      |       8 |

| utf8mb4_lithuanian_ci  | utf8mb4 | 236 |         | Yes      |       8 |

| utf8mb4_slovak_ci      | utf8mb4 | 237 |         | Yes      |       8 |

| utf8mb4_spanish2_ci    | utf8mb4 | 238 |         | Yes      |       8 |

| utf8mb4_roman_ci       | utf8mb4 | 239 |         | Yes      |       8 |

| utf8mb4_persian_ci     | utf8mb4 | 240 |         | Yes      |       8 |

| utf8mb4_esperanto_ci   | utf8mb4 | 241 |         | Yes      |       8 |

| utf8mb4_hungarian_ci   | utf8mb4 | 242 |         | Yes      |       8 |

| utf8mb4_sinhala_ci     | utf8mb4 | 243 |         | Yes      |       8 |

| utf8mb4_german2_ci     | utf8mb4 | 244 |         | Yes      |       8 |

| utf8mb4_croatian_ci    | utf8mb4 | 245 |         | Yes      |       8 |

| utf8mb4_unicode_520_ci | utf8mb4 | 246 |         | Yes      |       8 |

| utf8mb4_vietnamese_ci  | utf8mb4 | 247 |         | Yes      |       8 |

+------------------------+---------+-----+---------+----------+---------+

26 rows in set (0.00 sec)

* 修改默认排序规则mysql> set names utf8mb4 collate utf8mb4_bin;

Query OK, 0 rows affected (0.00 sec)

mysql> show character set like 'utf8mb4';

+---------+---------------+--------------------+--------+

| Charset | Description   | Default collation  | Maxlen |

+---------+---------------+--------------------+--------+

| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci |      4 |

+---------+---------------+--------------------+--------+

1 row in set (0.00 sec)

6. 总结

以需求驱动技术，技术本身没有优略之分，只有业务之分。