MySQL 数据类型

MySQL支持的数据类型

MySQL数据类型:数值型、字符串类型、日期和时间类型。

数值类型

整数类型 字节 最小值 最大值
TINYINT 1 有符号 -128 </br> 无符号 0 有符号 127 </br> 无符号 255
SMALLINT 2 有符号 -32768 </br> 无符号 0 有符号 32767 </br> 无符号 65535
MEDIUMINT 3 有符号 -8388608 </br> 无符号 0 有符号 8388607 </br> 无符号 1677215
INT、INTEGER 4 有符号 -2147483648 </br> 无符号 0 有符号 2147483647 </br> 无符号 4294967295
BIGINT 8 有符号 -9223372036854775808 </br> 无符号 0 有符号 9223372036854775807 </br> 无符号 18446744073709551615
浮点数类型 字节 最小值 最大值
FLOAT 4 ±1.175494351E-38 ±3.402823466E+38
DOUBLE 8 ±2.2250738585072014E-308 ±1.7976931348623157E+308
定点数类型 字节 描述
DEC(M,D) DECIMAL(M,D) M+2 最大取值范围与 DOUBLE 相同,给定 DECIMAL 的有效取值范围由M和D决定
位类型 字节 最小值 最大值
BIT(M) 1~8 BIT(1) BIT(64)

整数

MySQL支持在类型后面的小括号内指定显示宽度,而不是占用的字节数。
使用zerofill关键字在数字位数不够的控件用”0”填充。
所有整型类型有一个可选属性UNSIGNED(无符号),在字段中保存非负数或者较大上限值时可以使用此选项,其取值范围是正常值的下限取0,上限取原值的两倍。

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
MariaDB [test]> create table test_int(a int(5), b int(11));
Query OK, 0 rows affected (0.09 sec)

MariaDB [test]> insert into test_int values(1,1), (567789, 134567897531);
Query OK, 2 rows affected, 1 warning (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 1

MariaDB [test]> select * from test_int;
+--------+------------+
| a | b |
+--------+------------+
| 1 | 1 |
| 567789 | 2147483647 |
+--------+------------+
2 rows in set (0.00 sec)

MariaDB [test]> alter table test_int modify a int(5) zerofill;
Query OK, 2 rows affected (0.18 sec)
Records: 2 Duplicates: 0 Warnings: 0

MariaDB [test]> alter table test_int modify b int zerofill;
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0

MariaDB [test]> select * from test_int;
+----------+------------+
| a | b |
+----------+------------+
| 00001 | 0000000001 |
| 567789 | 2147483647 |
+----------+------------+
2 rows in set (0.01 sec)

MariaDB [test]> alter table test_int modify a int(5) unsigned zerofill;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [test]> alter table test_int modify b int(11) unsigned zerofill;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [test]> insert into test_int values(1,1), (56457789, 1345678972531);
Query OK, 2 rows affected, 1 warning (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 1

MariaDB [test]> select * from test_int;
+----------+-------------+
| a | b |
+----------+-------------+
| 00001 | 00000000001 |
| 56457789 | 04294967295 |
+----------+-------------+
2 rows in set (0.01 sec)

AUTO_INCREMENT属性,可以产生唯一标识符或顺序值, 只能用于整数类型。AUTO_INCREMENT值一般从1开始,每行增加1。一个表中最多只能有一个AUTO_INCREMENT列。对于任何想要使用AUTO_INCREMENT的列,应该定义为NOT NULL,并定义为PRIMARY KEY或定义为UNIQUE键。

1
2
3
CREATE TABLE AI (ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY);
CREATE TABLE AI(ID INT AUTO_INCREMENT NOT NULL ,PRIMARY KEY(ID));
CREATE TABLE AI (ID INT AUTO_INCREMENT NOT NULL ,UNIQUE(ID));

浮点数、定点数、BIT

小数的表示:浮点数和定点数。浮点数:float、double。定点数:decimal
定点数在MySQL内部以字符串形式存放,比浮点数更精确,适合用来存放货比等精度高的数据。
浮点数和定点数都可以用类型名称后加”(M,D)”的方式来进行表示,”(M,D)”表示该值一共显示M位数字(整数位+小数位),其中D位位于小数点后面,M和D又称为精度和标度。浮点数后跟”(M,D)”的方法是非标准用法,如果要用于数据库的迁移,最好不要使用。float和double不指定精度按照实际精度(由实际的硬件和操作系统决定)来表示,而decimal在不指定精度时,默认整数位为10,默认小数位为0。

  1. 浮点数存在误差
  2. 对货币等敏感数据应该采用定点数表示或存储。
  3. 编程中,如果用到浮点数,注意误差问题,避免浮点数做比较,浮点数的比较最好使用范围比较,而不使用”=”比较
  4. 注意浮点数中一些特殊值的处理
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
MariaDB [test]> show create table test_xiaoshu\G
*************************** 1. row ***************************
Table: test_xiaoshu
Create Table: CREATE TABLE `test_xiaoshu` (
`a` float(5,2) DEFAULT NULL,
`b` double(5,2) DEFAULT NULL,
`c` decimal(5,2) DEFAULT NULL,
`d` float DEFAULT NULL,
`e` double DEFAULT NULL,
`f` decimal(10,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

MariaDB [test]> insert into test_xiaoshu value(1.23, 1.23, 1.23, 1.23, 1.23, 1.23);
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [test]> select * from test_xiaoshu;
+------+------+------+------+------+------+
| a | b | c | d | e | f |
+------+------+------+------+------+------+
| 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1 |
+------+------+------+------+------+------+
1 row in set (0.00 sec)

MariaDB [test]> insert into test_xiaoshu value(1.234, 1.234, 1.234, 1.234, 1.234, 1.234);
Query OK, 1 row affected, 2 warnings (0.00 sec)

MariaDB [test]> select * from test_xiaoshu;
+------+------+------+-------+-------+------+
| a | b | c | d | e | f |
+------+------+------+-------+-------+------+
| 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1 |
| 1.23 | 1.23 | 1.23 | 1.234 | 1.234 | 1 |
+------+------+------+-------+-------+------+
2 rows in set (0.00 sec)

BIT(位类型),用于存放位字段值,BIT(M)可以用来存放多位二进制数,M范围从1~64,如果不写默认1位。对于位类型,直接使用select命令无法看到结果,可以用bin()(二进制格式)或者hex()(十六进制格式)函数进行读取。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

MariaDB [test]> desc test_bit;
+-------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| a | bit(1) | YES | | NULL | |
| b | bit(2) | YES | | NULL | |
| c | bit(3) | YES | | NULL | |
+-------+--------+------+-----+---------+-------+
3 rows in set (0.00 sec)

MariaDB [test]> insert into test_bit values(1, 1, 1), (2, 2, 2), (8, 8, 8);
Query OK, 3 rows affected, 4 warnings (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 4

MariaDB [test]> select bin(a), bin(b), bin(c), hex(a), hex(b), hex(c) from test_bit;
+--------+--------+--------+--------+--------+--------+
| bin(a) | bin(b) | bin(c) | hex(a) | hex(b) | hex(c) |
+--------+--------+--------+--------+--------+--------+
| 1 | 1 | 1 | 1 | 1 | 1 |
| 1 | 10 | 10 | 1 | 2 | 2 |
| 1 | 11 | 111 | 1 | 3 | 7 |
+--------+--------+--------+--------+--------+--------+
3 rows in set (0.00 sec)

日期时间类型

日期和时间类型 字节 最小值 最大值 零值表示
DATE 4 1000-01-01 9999-12-31 0000-00-00
DATETIME 8 1000-01-01 00:00:00 9999-12-31 23:59:59 0000-00-00 00:00:00
TIMESTAMP 4 19700101080001 2038年某个时刻 00000000000000
TIME 3 -838:59:59 838:59:59 00:00:00
YEAR 1 1901 2155 0000
  • 如果表示年月日,通常用DATE来表示
  • 如果表示年月日时分秒,通常用DATETIME来表示
  • 如果表示时分秒,通常用TIME来表示
  • 如果需要经常插入或者更新日期位当前系统时间,则通常使用TIMESTAMP来表示
  • 如果只表示年份,可以用YEAR来表示。

字符串类型

字符串类型 字节 描述及存储需求
CHAR(M) M M 为 0~255 之间的整数
VARCHAR(M) M 为 0~65535 之间的整数,值的长度+1 个字节
TINYBLOB 允许长度 0~255 字节,值的长度+1 个字节
BLOB 允许长度 0~65535 字节,值的长度+2 个字节
MEDIUMBLOB 允许长度 0~167772150 字节,值的长度+3 个字节
LONGBLOB 允许长度 0~4294967295 字节,值的长度+4 个字节
TINYTEXT 允许长度 0~255 字节,值的长度+2 个字节
TEXT 允许长度 0~65535 字节,值的长度+2 个字节
MEDIUMTEXT 允许长度 0~167772150 字节,值的长度+3 个字节
LONGTEXT 允许长度 0~4294967295 字节,值的长度+4 个字节
VARBINARY(M) 允许长度 0~M 个字节的变长字节字符串,值的长度+1 个字节
BINARY(M) M 允许长度 0~M 个字节的定长字节字符串

char和varchar

char和varchar都用来存储较短的字符串。主要区别是存储方式的不同:char列的长度固定为创建表时声明的长度,长度可以为从0~255的任意值;varchar列中的值为可变长字符串。检索时char列删除末尾的空格,而varchar则保留空格。
对于长度变化不大并且对查询速度要求较高的数据可以考虑使用char类型存储。
不同存储引擎对char和varchar使用原则不同:

  1. MyISAM:建议使用固定长度的数据列代替可变长度的数据列
  2. MEMORY:目前都使用固定长度的数据行处理,因为无论使用char或varchar列都没有关系,两者都是作为char类型处理
  3. InnoDB:建议使用varchar。InnoDB内部的行存储格式没有区分固定长度和可变长度列(所有数据行都使用指向数据列值的头指针)。因此,使用cahr和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
26
27
28
29
30
31
32
33
34
35
36
MariaDB [test]> desc test_char;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| a | varchar(4) | YES | | NULL | |
| b | char(4) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

MariaDB [test]> insert into test_char values("nnnn", "nnnn"), ("mm", "mm"), ("kkkkkk", "kkkkk"), ("ee ","ee "), (" tt", " tt");
Query OK, 5 rows affected, 2 warnings (0.03 sec)
Records: 5 Duplicates: 0 Warnings: 2

MariaDB [test]> select * from test_char;
+------+------+
| a | b |
+------+------+
| nnnn | nnnn |
| mm | mm |
| kkkk | kkkk |
| ee | ee |
| tt | tt |
+------+------+
5 rows in set (0.00 sec)

MariaDB [test]> select concat(a, '+'), concat(b, '+'), length(a), length(b) from test_char;
+----------------+----------------+-----------+-----------+
| concat(a, '+') | concat(b, '+') | length(a) | length(b) |
+----------------+----------------+-----------+-----------+
| nnnn+ | nnnn+ | 4 | 4 |
| mm+ | mm+ | 2 | 2 |
| kkkk+ | kkkk+ | 4 | 4 |
| ee + | ee+ | 3 | 2 |
| tt+ | tt+ | 3 | 3 |
+----------------+----------------+-----------+-----------+
5 rows in set (0.00 sec)

binary和varbinary类型

binary和varbinary包含二进制字符串,而不包含非二进制字符串。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
MariaDB [test]> desc test_binary;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| a | binary(4) | YES | | NULL | |
| b | varbinary(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

MariaDB [test]> insert into test_binary values("a", "a"), ("bbbb", "bbbb"), ("cc ", "cc "), ("dddddd", "ddddd");
Query OK, 4 rows affected, 2 warnings (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 2

MariaDB [test]> select *, hex(a), hex(b), concat(a, "+"), concat(b, "+"), length(a), length(b) from test_binary;
+------+------+----------+----------+----------------+----------------+-----------+-----------+
| a | b | hex(a) | hex(b) | concat(a, "+") | concat(b, "+") | length(a) | length(b) |
+------+------+----------+----------+----------------+----------------+-----------+-----------+
| a | a | 61000000 | 61 | a + | a+ | 4 | 1 |
| bbbb | bbbb | 62626262 | 62626262 | bbbb+ | bbbb+ | 4 | 4 |
| cc | cc | 63632020 | 63632020 | cc + | cc + | 4 | 4 |
| dddd | dddd | 64646464 | 64646464 | dddd+ | dddd+ | 4 | 4 |
+------+------+----------+----------+----------------+----------------+-----------+-----------+
4 rows in set (0.00 sec)

text和blob

保存较大文本时使用text或blob,blob能用来存储二进制数据,text只能保存字符数据。

  1. blob和text会引起性能问题,特别是执行大量的删除操作时。删除操作会在数据表中留下很大的”空洞”,后续插入数据插入”空洞”时,性能会有影响。可以定期使用OPTIMIZE和TABLE功能进行碎片整理,避免因为”空洞”导致性能问题。
  2. 可以使用合成(Synthetic)索引来提高大文本字段的查询性能。即生成散列值,然后保存在单独列中,在通过散列值查找数据(只能用于精确查找)。对大文本的模糊索引可以使用前缀索引来解决。
  3. 在不必要的情况下避免索引大型的blob或者text值。
  4. 把blob或text列分离到单独的表中。

ENUM类型

emum枚举类型,取值范围在创建表是通过枚举方式显示指定,对1~255个成员的枚举需要1个字节存储,对于255~65535个成员需要2个字节存储。最多允许65535个成员。

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
MariaDB [test]> desc test_enum;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| a | enum('A','B') | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.00 sec)

MariaDB [test]> insert into test_enum values("A"), ("b"), ("W"), (NULL);
Query OK, 4 rows affected, 1 warning (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 1

MariaDB [test]> show warnings;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'a' at row 3 |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> select * from test_enum;
+------+
| a |
+------+
| A |
| B |
| |
| NULL |
+------+
4 rows in set (0.00 sec)

枚举类型忽略大小写

SET类型

set类型也是字符串对象,里面包含0~64个成员。根据成员不同,存储上也有所不同。
1~8个成员的集合,占1个字节
9~16个成员的集合,占2个字节
17~24成员的集合,占3个字节
25~32成员的集合,占4个字节
33~64成员的集合,占8个字节
set和enum,存储不同,而且set一次可以取多个成员,enum只能选一个。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
MariaDB [test]> desc test_set;
+-------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| a | set('a','b','c','d') | YES | | NULL | |
+-------+----------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

MariaDB [test]> insert into test_set values('a,b'), ('a,b,a'), ('a,c'), ('a'), ('f'), ('a, b');
Query OK, 6 rows affected, 2 warnings (0.03 sec)
Records: 6 Duplicates: 0 Warnings: 2

MariaDB [test]> select * from test_set;
+------+
| a |
+------+
| a,b |
| a,b |
| a,c |
| a |
| |
| a |
+------+
6 rows in set (0.00 sec)

set所取成员中间不能有空格
set取成员中不存在的值会自动过滤掉
set取重复的成员只取一个