MySQL数据类型小结

备注:测试数据库版本为MySQL 8.0

这个blog我们来聊聊MySQL支持的数据类型

Table of Contents

• 一.数字类型
• 二.日期时间类型
• 三.字符类型

一.数字类型

总结:
1.不允许负数
整数类型有可选的UNSIGNED属性，表示不允许存负值，这大概可以使整数的上限提高一倍。
如: TINYINT UNSIGNED

2.如何选择合适的数值类型
1.整数类型
例如枚举类的， 选择 tinyint、smallint即可，节省磁盘空间就是优化。
其它的业务相关表，例如用户表、订单表 可以选择用 int类型。
虽然int类型不支持小数，但是例如金额这个，可以通过调整单位，例如单位为分，这样就可以存小数金额了
对于一些大的日志表、分布式ID之类的，可以选择bigint类型

2.小数类型
对于需要存储小数的场景而言，使用decimal(m,d)
m代表总的位数，d代表小数位，整数位为m-d

create table t_decimal(id  decimal(7,4));
-- 小数位不够会自动填0
insert into t_decimal values (123.456);
-- 这个是正常的插入方法
insert into t_decimal values (123.4561);
-- 整数为超过限制 报错
insert into t_decimal values (1230.4561);
-- 小数位超过限制 报错
insert into t_decimal values (1230.45612);

执行记录:

mysql> create table t_decimal(id  decimal(7,4));
Query OK, 0 rows affected (0.03 sec)

mysql> -- 小数位不够会自动填0
mysql> insert into t_decimal values (123.456);
Query OK, 1 row affected (0.00 sec)

mysql> -- 这个是正常的插入方法
mysql> insert into t_decimal values (123.4561);
Query OK, 1 row affected (0.00 sec)

mysql> -- 整数为超过限制 报错
mysql> insert into t_decimal values (1230.4561);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> -- 小数位超过限制 报错
mysql> insert into t_decimal values (1230.45612);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql>
mysql> select * from t_decimal;
+----------+
| id       |
+----------+
| 123.4560 |
| 123.4561 |
+----------+
2 rows in set (0.00 sec)

3.浮点数
浮点数会存在一定的精度的缺失，但是可以应用于科学计算，性能会比整数和小数类都要快。

二.日期时间类型

关于时间类型的选择:
1.如果只存年，用YEAR类型
2.如果只存年月日，用DATE
3.如果需要存年月日时分秒，用TIMESTAMP
不要被这个2038年给吓到了，而不用TIMESTAMP，其实更节约存储空间，且能容纳时区信息
4.不用将TIMESTAMP转换为数值
FROM_UNIXTIME() – 把数值转换为时间戳
UNIX_TIMESTAMP() – 把时间戳转换为数值

转换感觉是节省了空间，不过处理起来非常的不方便，不推荐使用

三.字符类型

CHAR和VARCHAR类型

1.类型选择问题
很多时候，开发同事为了方便，直接用varchar(200) 来存储字符，不考虑实际需求。
这样做，存在诸多弊端。
如果是md5密码这样的定长字段，如果用varchar类型，会浪费一定的存储空间。
如果存储的字符只有5个，而这时都用varchar(200)，感觉存储空间是一样的。但是程序端读取的时候，varchar(200)会消耗更多的内存。

2.变长字符的更新问题
InnoDB存储引擎
varchar由于是变长，遇到更新的时候，如果比原先的长度长很多，这个时候页的空间不够，会分裂页，此时会比较消耗性恶NGN

3.定长字符神奇的空格问题

-- char类型，string3末尾的空格莫名的不见了
create table char_test( char_col char(10));
insert into char_test(char_col) values ('string1'),('   string2'),('string3   ');
select * from char_test;
select concat("'",char_col,"'") from char_test;
-- 换成varchar类型，就不会出现这个问题
drop table char_test;
create table char_test( char_col varchar(10));
insert into char_test(char_col) values ('string1'),('   string2'),('string3   ');
select concat("'",char_col,"'") from char_test;

执行记录:

mysql> create table char_test( char_col char(10));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into char_test(char_col) values ('string1'),('   string2'),('string3   ');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from char_test;
+------------+
| char_col   |
+------------+
| string1    |
|    string2 |
| string3    |
+------------+
3 rows in set (0.00 sec)

mysql> select concat("'",char_col,"'") from char_test;
+--------------------------+
| concat("'",char_col,"'") |
+--------------------------+
| 'string1'                |
| '   string2'             |
| 'string3'                |
+--------------------------+
3 rows in set (0.00 sec)

mysql>
mysql>
mysql> drop table char_test;
Query OK, 0 rows affected (0.03 sec)

mysql> create table char_test( char_col varchar(10));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into char_test(char_col) values ('string1'),('   string2'),('string3   ');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select concat("'",char_col,"'") from char_test;
+--------------------------+
| concat("'",char_col,"'") |
+--------------------------+
| 'string1'                |
| '   string2'             |
| 'string3   '             |
+--------------------------+
3 rows in set (0.00 sec)

BLOB和TEXT类型

BLOB是SMALLBLOB的同义词
TEXT是SMALLTEXT的同义词

MySQL把每个BLOB和TEXT当做一个独立的对象处理。
当BLOB和TEXT值太大时，InnoDB会使用专门的外部存储区域来进行存储，此时每个值在行内需要1-4个值存储一个指针，然后在外部存储区域存储实际的值

BLOB和TEXT家族之间仅有的不同是BLOB类型存储的是二进制数据，没有排序规则或字符集，而TEXT类型有字符集和排序规则。

ENUM和SET类型

CREATE TABLE test_enum_set (
  a SET('A','B','C'),
  b ENUM('张三','李四')
);


insert into test_enum_set values ('A','张三');

insert into test_enum_set values ('A,B','张三');

insert into test_enum_set values ('A,B','张三,李四');

执行记录:

mysql> CREATE TABLE test_enum_set (
    ->   a SET('A','B','C'),
    ->   b ENUM('张三','李四')
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test_enum_set values ('A','张三');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test_enum_set values ('A,B','张三');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test_enum_set values ('A,B','张三,李四');
ERROR 1265 (01000): Data truncated for column 'b' at row 1
mysql>
mysql> select * from test_enum_set;
+------+--------+
| a    | b      |
+------+--------+
| A    | 张三   |
| A,B  | 张三   |
+------+--------+
2 rows in set (0.00 sec)