/ mysql  

MySQL数据类型

MySQL学习笔记(Day008:数据类型)

@(MySQL学习)

[TOC]

一. INT类型

1. INT类型的分类

  • TINYINT

    • 存储空间 : 1 字节
    • 取值范围
      • 有符号(signed) : [-128, 127]
      • 无符号(unsigned) :[0, 255]
  • SMALLINT

    • 存储空间 : 2 字节
    • 取值范围
      • 有符号(signed) : [-32768, 32767]
      • 无符号(unsigned) :[0, 65535]
  • MEDIUMINT

    • 存储空间 : 3 字节
    • 取值范围
      • 有符号(signed) : [-8388608, 8388607]
      • 无符号(unsigned) :[0, 16777215]
  • INT

    • 存储空间 : 4 字节
    • 取值范围
      • 有符号(signed) : [-2147483648, 2147483647]
      • 无符号(unsigned) :[0, 4294967295]
  • BIGINT

    • 存储空间 : 8 字节
    • 取值范围
      • 有符号(signed) : [-9223372036854775808, 9223372036854775807]
      • 无符号(unsigned) :[0, 18446744073709551615]

2. INT类型的使用

  • 自增长ID

    • 推荐使用BIGINT,而不是INT;
  • unsigned or signed

    • 根据实际情况使用,一般情况下推荐默认sigend
    • unsigned 的注意事项
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql> create table test_unsigned(a int unsigned, b int unsigned);
Query OK, 0 rows affected (0.14 sec)

mysql> insert into test_unsigned values(1, 2);
Query OK, 1 row affected (0.03 sec)

mysql> select a - b from test_unsigned;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`burn_test`.`test_unsigned`.`a` - `burn_test`.`test_unsigned`.`b`)'

mysql> select b - a from test_unsigned;
+-------+
| b - a |
+-------+
| 1 |
+-------+
1 row in set (0.00 sec)

mysql> set sql_mode = 'no_unsigned_subtraction'; -- 这样就可以得到负数
Query OK, 0 rows affected (0.00 sec)

mysql> select a - b from test_unsigned;
+-------+
| a - b |
+-------+
| -1 |
+-------+
1 row in set (0.00 sec)

一般情况下使用int时,推荐有符号数(signed), 使用无符号数只是比原来多一倍的取值,数量级上没有改变。

如果需要取值范围很大,直接选择用BIGINT

3. INT(N)

1
2
3
4
5
6
7
8
9
10
mysql> show create table  test_unsigned;
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| test_unsigned | CREATE TABLE `test_unsigned` (
`a` int(10) unsigned DEFAULT NULL,
`b` int(10) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
  • int(N) 和 zerofill
    • int(N)中的N是显示宽度,不表示存储的数字的长度的上限。
    • zerofill表示当存储的数字长度 < N时,用数字0填充左边,直至补满长度N
    • 当存储数字的长度超过N时,按照实际存储的数字显示
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
mysql> create  table  test_int_n(a int(3) zerofill);  -- 显示宽度N=3
Query OK, 0 rows affected (0.11 sec)

mysql> insert into test_int_n values(1);
Query OK, 1 row affected (0.04 sec)

mysql> select * from test_int_n;
+------+
| a |
+------+
| 001 | -- 不满 N=3时,左边用0填充
+------+
1 row in set (0.00 sec)

mysql> insert into test_int_n values(1111);
Query OK, 1 row affected (0.03 sec)

mysql> select * from test_int_n;
+------+
| a |
+------+
| 001 |
| 1111 | -- 超过N=3的长度时,是什么数字,显示什么数字
+------+
2 rows in set (0.00 sec)

mysql> select a, HEX(a) from test_int_n\G
*************************** 1. row ***************************
a: 001
HEX(a): 1 -- 实际存储的还是1
*************************** 2. row ***************************
a: 1111
HEX(a): 457 -- 1111对应的16进制就是457
2 rows in set (0.00 sec)

int(N)中的Nzerofill配合才有意义,且仅仅是显示的时候才有意义,和实际存储没有关系,不会去截取数字的长度。

4. AUTO_INCREMENT

  • 自增
  • 每张表一个
  • 必须是索引的一部分
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
mysql> create table test_auto_increment(a int auto_increment);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
-- 没有指定为key,报错了

mysql> create table test_auto_increment(a int auto_increment primary key); -- 指定为key后有效
Query OK, 0 rows affected (0.11 sec)

mysql> insert into test_auto_increment values(NULL); -- 插入NULL值
Query OK, 1 row affected (0.03 sec)

mysql> select * from test_auto_increment;
+---+
| a |
+---+
| 1 | -- 插入NULL值,便可以让其自增,且默认从1开始
+---+
1 row in set (0.00 sec)

mysql> insert into test_auto_increment values(0); -- 插入 0
Query OK, 1 row affected (0.03 sec)

mysql> select * from test_auto_increment;
+---+
| a |
+---+
| 1 |
| 2 | -- 插入 0 ,自增长为2
+---+
2 rows in set (0.00 sec)

mysql> insert into test_auto_increment values(-1); -- 插入 -1
Query OK, 1 row affected (0.02 sec)

mysql> select * from test_auto_increment;
+----+
| a |
+----+
| -1 | -- 刚刚插入的-1
| 1 |
| 2 |
+----+
3 rows in set (0.00 sec)

mysql> insert into test_auto_increment values(NULL); -- 继续插入NULL
Query OK, 1 row affected (0.02 sec)

mysql> select * from test_auto_increment;
+----+
| a |
+----+
| -1 |
| 1 |
| 2 |
| 3 | -- 刚刚插入NULL, 自增为3
+----+
4 rows in set (0.00 sec)

mysql> insert into test_auto_increment values('0'); -- 插入字符0
Query OK, 1 row affected (0.04 sec)

mysql> select * from test_auto_increment;
+----+
| a |
+----+
| -1 |
| 1 |
| 2 |
| 3 |
| 4 | -- 插入字符'0' 后, 自增长为4
+----+
5 rows in set (0.00 sec)

mysql> update test_auto_increment set a = 0 where a = -1; -- 更新为0
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from test_auto_increment;
+---+
| a |
+---+
| 0 | -- 原来的 -1 更新为0
| 1 |
| 2 |
| 3 |
| 4 |
+---+
5 rows in set (0.00 sec)

--
-- 数字 0 这个值比较特殊, 插入0和插入NULL的效果是一样的,都是代表自增
--

-----

mysql> insert into test_auto_increment values(NULL), (100), (NULL);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from test_auto_increment;
+-----+
| a |
+-----+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 | -- 第一个NULL
| 100 | -- 100
| 101 | -- 第二个NULL, 按当前最大的值 +1来设置,之前是100,所以这里101
+-----+
8 rows in set (0.00 sec)

mysql> insert into test_auto_increment values(99); -- 插入99
Query OK, 1 row affected (0.02 sec)

mysql> select * from test_auto_increment;
+-----+
| a |
+-----+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 99 | -- 刚刚插入的 99
| 100 |
| 101 |
+-----+
9 rows in set (0.00 sec)

AUTO_INCREMENT是实例启动时,取当前表的最大值,然后 +1 即为下次自增的值。(MAX + 1)

TIPS:
insert into tablename select NULL; 等价与 insert into tablename values (NULL);


二. 数字类型

1. 数字类型的分类

  • 单精度类型:FLOAT

    • 存储空间:4 字节
    • 精确性:低
  • 双精度类型:DOUBLE

    • 占用空间:8 字节
    • 精确性:低,比FLOAT高
  • 高精度类型:DECIMAL

    • 占用空间:变长
    • 精确性:非常高

注意:财务系统必须使用DECIMAL


三. 字符串类型

1. 字符串类型介绍

类型 说明 N的含义 是否有字符集 最大长度
CHAR(N) 定长字符 字符 255
VARCHAR(N) 变长字符 字符 16384
BINARY(N) 定长二进制字节 字节 255
VARBINARY(N) 变长二进制字节 字节 16384
TINYBLOB(N) 二进制大对象 字节 256
BLOB(N) 二进制大对象 字节 16K
MEDIUMBLOB(N) 二进制大对象 字节 16M
LONGBLOB(N) 二进制大对象 字节 4G
TINYTEXT(N) 大对象 字节 256
TEXT(N) 大对象 字节 16K
MEDIUMTEXT(N) 大对象 字节 16M
LONGTEXT(N) 大对象 字节 4G

2. N和字符集

  • char(N)
    • 假设当前table的字符集的最大长度W, 则char(N)的最大存储空间为 (N X W)Byte;假设使用UTF-8,则char(10)可以最小存储10个字节的字符,最大存储30个字节的字符,其实是另一种意义上的varchar
    • 当存储的字符数小于N时,尾部使用空格填充,并且填充最小字节的空格
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
mysql> create table test_char(a char(10));
Query OK, 0 rows affected (0.12 sec)

mysql> show create table test_char;
+-----------+------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+------------------------------------------------------------------------------------------------+
| test_char | CREATE TABLE `test_char` (
`a` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-----------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into test_char values('abc');
Query OK, 1 row affected (0.02 sec)

mysql> insert into test_char values('你好吗');
Query OK, 1 row affected (0.05 sec)

mysql> insert into test_char values('大家好ab');
Query OK, 1 row affected (0.02 sec)

mysql> insert into test_char values('大家ab好');
Query OK, 1 row affected (0.03 sec)

mysql> insert into test_char values('大家ab好吗');
Query OK, 1 row affected (0.03 sec)

mysql> select a, length(a) from test_char;
+----------------+-----------+
| a | length(a) |
+----------------+-----------+
| abc | 3 |
| 你好吗 | 9 |
| 大家好ab | 11 |
| 大家ab好 | 11 |
| 大家ab好吗 | 14 |
+----------------+-----------+
5 rows in set (0.00 sec)

mysql> select a, hex(a) from test_char;
+----------------+------------------------------+
| a | hex(a) |
+----------------+------------------------------+
| abc | 616263 | -- 注意这里,以及下面的16进制值,一会可以对比
| 你好吗 | E4BDA0E5A5BDE59097 |
| 大家好ab | E5A4A7E5AEB6E5A5BD6162 |
| 大家ab好 | E5A4A7E5AEB66162E5A5BD |
| 大家ab好吗 | E5A4A7E5AEB66162E5A5BDE59097 |
+----------------+------------------------------+
5 rows in set (0.00 sec)

mysql> select hex(' ');
+----------+
| hex(' ') |
+----------+
| 20 | -- 注意 空格 空格对应的16进制数字是 20
+----------+
1 row in set (0.00 sec)

test_char表实际二进制存储文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 
-- shell> hexdump -C test_char.idb
--

-- 1:abc
-- 2:你好吗
-- 3:大家好ab
-- 4:大家ab好
-- 5:大家ab好吗

-- ---省略---
00006070 73 75 70 72 65 6d 75 6d 0a 00 00 00 10 00 24 00 |supremum......$.|
00006080 00 00 00 02 03 00 00 00 00 1f 33 a8 00 00 00 26 |..........3....&|
00006090 01 10 61 62 63 20 20 20 20 20 20 20 0a 00 00 00 |..abc ....| -- 1:后面补了7个空格
000060a0 18 00 24 00 00 00 00 02 04 00 00 00 00 1f 34 a9 |..$...........4.|
000060b0 00 00 00 25 01 10 e4 bd a0 e5 a5 bd e5 90 97 20 |...%........... | -- 2:补充了1个空格
000060c0 0b 00 00 00 20 00 25 00 00 00 00 02 05 00 00 00 |.... .%.........|
000060d0 00 1f 39 ac 00 00 00 26 01 10 e5 a4 a7 e5 ae b6 |..9....&........| -- 3:没有补充空格
000060e0 e5 a5 bd 61 62 0b 00 00 00 28 00 25 00 00 00 00 |...ab....(.%....| --
000060f0 02 06 00 00 00 00 1f 3a ad 00 00 00 28 01 10 e5 |.......:....(...| --
00006100 a4 a7 e5 ae b6 61 62 e5 a5 bd 0e 00 00 00 30 ff |.....ab.......0.| -- 4:没有补充空格
00006110 5f 00 00 00 00 02 07 00 00 00 00 1f 3f b0 00 00 |_...........?...|
00006120 00 29 01 10 e5 a4 a7 e5 ae b6 61 62 e5 a5 bd e5 |.)........ab....|--
00006130 90 97 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| -- 5:没有补充空格
00006140 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
-- ---省略---
  • varchar(N)
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    mysql> create table test_varchar(a varchar(10));
    Query OK, 0 rows affected (0.12 sec)

    mysql> show create table test_varchar;
    +--------------+------------------------------------------------------------------------------------------------------+
    | Table | Create Table |
    +--------------+------------------------------------------------------------------------------------------------------+
    | test_varchar | CREATE TABLE `test_varchar` (
    `a` varchar(10) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
    +--------------+------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

    mysql> insert into test_varchar values('abc');
    Query OK, 1 row affected (0.03 sec)

    mysql> insert into test_varchar values('你好吗');
    Query OK, 1 row affected (0.02 sec)

    mysql> insert into test_varchar values('大家好ab');
    Query OK, 1 row affected (0.03 sec)

    mysql> insert into test_varchar values('大家ab好');
    Query OK, 1 row affected (0.02 sec)

    mysql> insert into test_varchar values('大家ab好吗');
    Query OK, 1 row affected (0.03 sec)

    mysql> select a, hex(a) from test_varchar;
    +----------------+------------------------------+
    | a | hex(a) |
    +----------------+------------------------------+
    | abc | 616263 |
    | 你好吗 | E4BDA0E5A5BDE59097 |
    | 大家好ab | E5A4A7E5AEB6E5A5BD6162 |
    | 大家ab好 | E5A4A7E5AEB66162E5A5BD |
    | 大家ab好吗 | E5A4A7E5AEB66162E5A5BDE59097 |
    +----------------+------------------------------+
    5 rows in set (0.00 sec)

    mysql> select a, length(a) from test_varchar;
    +----------------+-----------+
    | a | length(a) |
    +----------------+-----------+
    | abc | 3 |
    | 你好吗 | 9 |
    | 大家好ab | 11 |
    | 大家ab好 | 11 |
    | 大家ab好吗 | 14 |
    +----------------+-----------+
    5 rows in set (0.00 sec)

test_varchar表实际二进制存储文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- 
-- shell> hexdump -C test_char.idb
--

-- 1:abc
-- 2:你好吗
-- 3:大家好ab
-- 4:大家ab好
-- 5:大家ab好吗

-- 和char一样观察,都没有进行空格的填充

00006070 73 75 70 72 65 6d 75 6d 03 00 00 00 10 00 1d 00 |supremum........|
00006080 00 00 00 02 08 00 00 00 00 1f 44 b5 00 00 00 29 |..........D....)|
00006090 01 10 61 62 63 09 00 00 00 18 00 23 00 00 00 00 |..abc......#....|
000060a0 02 09 00 00 00 00 1f 45 b6 00 00 00 2b 01 10 e4 |.......E....+...|
000060b0 bd a0 e5 a5 bd e5 90 97 0b 00 00 00 20 00 25 00 |............ .%.|
000060c0 00 00 00 02 0a 00 00 00 00 1f 4a b9 00 00 00 2c |..........J....,|
000060d0 01 10 e5 a4 a7 e5 ae b6 e5 a5 bd 61 62 0b 00 00 |...........ab...|
000060e0 00 28 00 25 00 00 00 00 02 0b 00 00 00 00 1f 4b |.(.%...........K|
000060f0 ba 00 00 00 2c 01 10 e5 a4 a7 e5 ae b6 61 62 e5 |....,........ab.|
00006100 a5 bd 0e 00 00 00 30 ff 67 00 00 00 00 02 0c 00 |......0.g.......|
00006110 00 00 00 1f 50 bd 00 00 00 2d 01 10 e5 a4 a7 e5 |....P....-......|
00006120 ae b6 61 62 e5 a5 bd e5 90 97 00 00 00 00 00 00 |..ab............|
00006130 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
  • 插入数据尾部带空格
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
mysql> insert into test_char values('好好好   ');  -- 后面有3个空格
Query OK, 1 row affected (0.03 sec)

mysql> insert into test_varchar values('好好好 '); -- 后面有3个空格
Query OK, 1 row affected (0.02 sec)

--
-- test_char 表
--
mysql> select a, length(a) from test_char;
+----------------+-----------+
| a | length(a) |
+----------------+-----------+
| abc | 3 |
| 你好吗 | 9 |
| 大家好ab | 11 |
| 大家ab好 | 11 |
| 大家ab好吗 | 14 |
| 好好好 | 9 | -- 只有9个字节
+----------------+-----------+
6 rows in set (0.00 sec)

mysql> select a, hex(a) from test_char;
+----------------+------------------------------+
| a | hex(a) |
+----------------+------------------------------+
| abc | 616263 |
| 你好吗 | E4BDA0E5A5BDE59097 |
| 大家好ab | E5A4A7E5AEB6E5A5BD6162 |
| 大家ab好 | E5A4A7E5AEB66162E5A5BD |
| 大家ab好吗 | E5A4A7E5AEB66162E5A5BDE59097 |
| 好好好 | E5A5BDE5A5BDE5A5BD | -- 无填充空格
+----------------+------------------------------+
6 rows in set (0.00 sec)


--
-- test_varchar表
--
mysql> select a, length(a) from test_varchar;
+----------------+-----------+
| a | length(a) |
+----------------+-----------+
| abc | 3 |
| 你好吗 | 9 |
| 大家好ab | 11 |
| 大家ab好 | 11 |
| 大家ab好吗 | 14 |
| 好好好 | 12 | -- (好好好)9个字节 + 3个字节的空格
+----------------+-----------+
7 rows in set (0.00 sec)

mysql> select a, hex(a) from test_varchar;
+----------------+------------------------------+
| a | hex(a) |
+----------------+------------------------------+
| abc | 616263 |
| 你好吗 | E4BDA0E5A5BDE59097 |
| 大家好ab | E5A4A7E5AEB6E5A5BD6162 |
| 大家ab好 | E5A4A7E5AEB66162E5A5BD |
| 大家ab好吗 | E5A4A7E5AEB66162E5A5BDE59097 |
| 好好好 | E5A5BDE5A5BDE5A5BD202020 | -- 后面有20 20 20 ,表示3个自己的空格
+----------------+------------------------------+
7 rows in set (0.00 sec)

上面的现象无法用统一的规则进行表述,但是官方文档给出的解释是,这样的安排是为了避免索引页的碎片

3.BLOB和TEXT

  • 在BLOB和TEXT上创建索引时,必须指定索引前缀的长度

    1
    2
    3
    4
    5
    mysql> create table test_text(a int primary key, b text, key(b));
    ERROR 1170 (42000): BLOB/TEXT column 'b' used in key specification without a key length

    mysql> create table test_text(a int primary key, b text, key(b(64)));
    Query OK, 0 rows affected (0.13 sec)
  • BLOB和TEXT列不能有默认值

  • BLOB和TEXT列排序时只使用该列的前max_sort_length个字节
    1
    2
    3
    4
    5
    6
    7
    mysql> select @@max_sort_length;
    +-------------------+
    | @@max_sort_length |
    +-------------------+
    | 1024 |
    +-------------------+
    1 row in set (0.00 sec)

不建议在MySQL中存储大型的二进制数据,比如歌曲,视频


四. 字符集

1. 常见的字符集

  • utf8
  • utf8mb4
  • gbk
  • gb18030
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 | -- gbk,表示的字符有限
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 | -- utf8,最长3字节
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 | -- utf8 + mobile端字符
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 | -- gb18030,最长4个字节
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)

2. collation

collation的含义是指排序规则,ci(case insensitive)结尾的排序集是不区分大小写的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> select 'a' = 'A';
+-----------+
| 'a' = 'A' |
+-----------+
| 1 | -- 因为大小写无关,所以返回1
+-----------+
1 row in set (0.00 sec)

mysql> create table test_ci (a varchar(10), key(a));
Query OK, 0 rows affected (0.13 sec)

mysql> insert into test_ci values('a');
Query OK, 1 row affected (0.02 sec)

mysql> insert into test_ci values('A');
Query OK, 1 row affected (0.03 sec)

mysql> select * from test_ci where a = 'a';
+------+
| a |
+------+
| a |
| A | -- A也被我们查到了
+------+
2 rows in set (0.00 sec)

上面的情况如果从业务的角度上看,可以很好理解,比如创建一个用户叫做Tom,你是不希望再创建一个叫做tom的用户的

  • 修改默认的collation
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    mysql> set names utf8mb4 collate utf8mb4_bin;  -- 当前会话有效
    Query OK, 0 rows affected (0.00 sec)

    mysql> select 'a' = 'A';
    +-----------+
    | 'a' = 'A' |
    +-----------+
    | 0 |
    +-----------+
    1 row in set (0.00 sec)

字符集的指定,可以在创建数据库的时候指定,也可以在创建表的时候单独指定,也可以创建列的时候进行指定


五. 集合类型

  • 集合类型ENUM 和 SET
  • ENUM类型最多允许65536个值
  • SET类型最多允许64个值
  • 通过sql_mode参数可以用户约束检查
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> create table test_col (
-> user varchar(10),
-> sex enum('male', 'female') -- 虽然写的是字符串,单其实存储的整型,效率还是可以的
-> );

mysql> insert into test_col values ("tom", "male");
Query OK, 1 row affected (0.02 sec)

mysql> insert into test_col values ("tom", "xmale"); -- 不是male 和 female
Query OK, 1 row affected, 1 warning (0.03 sec) -- 有warning

mysql> set sql_mode='strict_trans_tables'; -- 设置为严格模式
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> insert into test_col values ("tom", "xmale");
ERROR 1265 (01000): Data truncated for column 'sex' at row 1

强烈建议新业务上都设置成严格模式


六. 日期类型

日期类型 占用空间 表示范围
DATETIME 8 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
DATE 3 1000-01-01 ~ 9999-12-31
TIMESTAMP 4 1970-01-01 00:00:00UTC ~ 2038-01-19 03:14:07UTC
YEAR 1 YEAR(2):1970-2070, YEAR(4):1901-2155
TIME 3 -838:59:59 ~ 838:59:59

TIMESTAMP 带时区功能

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
mysql> create table test_time(a timestamp, b datetime);
Query OK, 0 rows affected (0.12 sec)

mysql> insert into test_time values (now(), now());
Query OK, 1 row affected (0.03 sec)

mysql> select * from test_time;
+---------------------+---------------------+
| a | b |
+---------------------+---------------------+
| 2015-11-28 10:00:39 | 2015-11-28 10:00:39 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> select @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| SYSTEM |
+-------------+
1 row in set (0.00 sec)

mysql> set time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| +00:00 |
+-------------+
1 row in set (0.00 sec)

mysql> select * from test_time;
+---------------------+---------------------+
| a | b |
+---------------------+---------------------+
| 2015-11-28 2:00:39 | 2015-11-28 10:00:39 | -- 时区的差别体现出来了
+---------------------+---------------------+
1 row in set (0.00 sec)