当前位置:Gxlcms > 数据库问题 > MySQL数据类型--------字符串类型实战

MySQL数据类型--------字符串类型实战

时间:2021-07-01 10:21:17 帮助过:4人阅读

  * 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. 总结


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


本文出自 “sea” 博客,请务必保留此出处http://lisea.blog.51cto.com/5491873/1943246

MySQL数据类型--------字符串类型实战

标签:mysql   dba   数据类型   

人气教程排行