网站建设资讯

NEWS

网站建设资讯

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

1. 背景

成都创新互联-云计算及IDC服务提供商,涵盖公有云、IDC机房租用、多线服务器托管、等保安全、私有云建设等企业级互联网基础服务,欢迎来电:18980820575

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


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


新闻标题:MySQL数据类型--------字符串类型实战
本文来源:http://njwzjz.com/article/jejooo.html