MySQL数据库之数据类型

数据类型

概念
- MySQL中的数据类型是强类型

数值型

整型

整型
- 范围要尽可能小，范围越小，占用空间越少
- 无符号整数是整数的两倍
- 整形支持显示宽度，显示宽带是最小的显示位数，必须结合zerofill才起作用

整型	占用字节数	范围
tinyint	1	-128~127
smallint	2	-32768~32767
mediumint	3	-8388608~8388607
int	4	-2147483648~2147483647
bigint	8	-9223372036854775808~9223372036854775807

-- 范围要尽可能小，范围越小，占用空间越少
mysql> create table stu1(
    -> id tinyint,      
    -> name varchar(20)
    -> );
# `Query OK, 0 rows affected (0.02 sec)`

-- 超出范围会报错
mysql> insert into stu1 values (128,'tom');
# `ERROR 1264 (22003): Out of range value for column 'id' at row 1`

-- 无符号整形就是没有负数，无符号整数是整数的两倍
mysql> create table stu2(
    -> id tinyint unsigned   
    -> );
# `Query OK, 0 rows affected (0.02 sec)`

mysql> insert into stu2 values (128);
# `Query OK, 1 row affected (0.00 sec)`

-- 添加前导0,int(5)显示宽带是5
mysql> create table stu4(
    -> id int(5),
    -> num int(5) zerofill   # 添加前导0,int(5)显示宽带是5
    -> );
# `Query OK, 0 rows affected (0.05 sec)`

mysql> insert into stu4 values (12,12);
# `Query OK, 1 row affected (0.00 sec)`

mysql> select * from stu4;
+------+-------+
| id   | num   |
+------+-------+
|   12 | 00012 |
+------+-------+
# `1 row in set (0.00 sec)`

浮点型

浮点型
- 浮点数有单精度和双精度
- 浮点数支持科学计数法
- 浮点数精度会丢失

浮点型	占用字节数	范围
float（单精度型）	4	-3.4E+38~3.4E+38
double（双精度型）	8	-1.8E+308~1.8E+308

浮点数的声明
- float(M, D) M:总位数 D:小数位数
- double(M,D) M:总位数 D:小数位数
- 不指定位数，默认是小数点后面6位 double默认是17位

-- 浮点数有单精度和双精度
mysql> create table stu5(
    -> num1 float(5,2),   -- 浮点数
    -> num2 double(6,1)	  -- 双精度数
    -> );
# `Query OK, 0 rows affected (0.05 sec)`

mysql> insert into stu5 values (3.1415,12.96);
# `Query OK, 1 row affected (0.00 sec)`

mysql> select * from stu5;
+------+------+
| num1 | num2 |
+------+------+
| 3.14 | 13.0 |
+------+------+
# `1 row in set (0.00 sec)`

-- MySQL浮点数支持科学计数法
mysql> create table stu6(
    -> num float   
    -> );
# `Query OK, 0 rows affected (0.03 sec)`

mysql> insert into stu6 values (5E2),(6E-2); 
# `Query OK, 2 rows affected (0.00 sec)`
# `Records: 2  Duplicates: 0  Warnings: 0`

mysql> select * from stu6;
+------+
| num  |
+------+
|  500 |
| 0.06 |
+------+
# `2 rows in set (0.00 sec)`

-- 浮点数精度会丢失
mysql> insert into stu6 values(99.999999999);
# `Query OK, 1 row affected (0.00 sec)`

mysql> select * from stu6;
+------+
| num  |
+------+
|  100 |
+------+

定点数

定点数(小数)
- 将整数部分和小数部分分开存储
- decimal是变长的，大致是每9个数字用4个字节存储，整数和小数分开计算
- M最大是65, D最大是30, 默认是(10,2)
- 定点和浮点都支持无符号、显示宽度0填充
定点数声明
- decimal(M,D) M:总位数 D:小数位数

-- 存放定点数
mysql> create table stu8(
    -> num decimal(20,9)  
    -> );
# `Query OK, 0 rows affected (0.00 sec)`

mysql> insert into stu8 values(12.999999999);
# `Query OK, 1 row affected (0.00 sec)`

mysql> select * from stu8;
+--------------+
| num          |
+--------------+
| 12.999999999 |
+--------------+
# `1 row in set (0.00 sec)`

字符型

字符型
- 在数据库中没有字符串概念, 只有字符, 所以数据库中只能用单引号
- 一条记录的总长度不能超过65535
- text系列在表中存储的是地址
- char是定长,char最大值是255
- var是变长,varchar最大值是65535，具体要看字符编码

数据类型	描述
char	定长字符，最大可以到255
varchar	可变长度字符，最大可以到65535
tinytext	2⁸–1 =255
text	2¹⁶–1 =65535
mediumtext	2²⁴–1
longtext	2³²–1

不同编码下的存储说明
- char(4) 存放4个字符,中英文一样
- varchar(L) 实现变长机制,需要额外的空间来记录数据真实的长度
- L的理论长度是65535,但事实上达不到,因为有的字符是多字节字符,所以L达不到65535

-- 在utf8编码下，一个字符占用3个字节，max=21845
MariaDB [stu]> create table stu1(
    -> name varchar(65535)
    -> )charset=utf8;
# `Query OK, 0 rows affected, 1 warning (0.036 sec)`

-- 在gbk下一个字符占用2个字节,max=32767
MariaDB [stu]> create table stu2(
    -> name varchar(65535)
    -> )charset=gbk;
# `Query OK, 0 rows affected, 1 warning (0.023 sec)`

-- text系列的类型在表中存储的是地址，占用大小大约10个字节
MariaDB [stu]> create table stu3(
    -> id int,
    -> name longtext
    -> );
# `Query OK, 0 rows affected (0.029 sec)`

-- 一个记录的所有字段的总长度也不能超过65535个字节
MariaDB [stu]> create table stu4(
    -> name varchar(65536),
    -> id int
    -> );
# `Query OK, 0 rows affected, 1 warning (0.021 sec)`

枚举

枚举(enum)
- 枚举是从集合中单选一个值作为数据
- 枚举值是通过整形数字来管理的,第一个值是1,第二个值是2,以此类推
- 枚举值在数据库存储的是整型数字
枚举的优点
- 限制值
- 节省空间
- 运行速度快(整型比字符型运行速度快)
思考：已知枚举占用两个字节,所以最多可以有多少个枚举值？
- 2字节=16位，2^16=65536,范围是(0-65535)
- 由于枚举从1开始,所以枚举值最多有65535个

mysql> create table stu12(
    -> name varchar(20),
    -> sex enum('男','女','保密') 
    -> );
# `Query OK, 0 rows affected (0.06 sec)`

-- 插入的枚举值只能是枚举中提供的选项
mysql> insert into stu12 values ('tom','男');
# `Query OK, 1 row affected (0.00 sec)`

-- 报错，只能插入男、女、保密
mysql> insert into stu12 values ('tom','不告诉你');
# `ERROR 1265 (01000): Data truncated for column 'sex' at row 1`

mysql> insert into stu12 values ('berry',2);  
# `Query OK, 1 row affected (0.00 sec)`

mysql> select * from stu12;
+-------+------+
| name  | sex   |
+-------+------+
| tom   | 男    |
| berry | 女    |
+-------+------+

mysql> select * from stu12 where sex=2;  
+-------+------+
| name  | sex  |
+-------+------+
| berry | 女   |
+-------+------+
# `1 row in set (0.00 sec)`

集合

集合(set)
- 从集合中选择一些值作为数据（多选）
- 集合和枚举一样，也为每个集合元素分配一个固定值
  - 分配方式是从前往后按2的0、1、2、…次方
  - 转换成二进制后只有一位是1，其他都是0
已知集合类型占8个字节，那么集合中最多有多少选项
- 有64个选项

-- 插入的顺序不一样，但显示的顺序一样
mysql> create table stu13(
    -> name varchar(20),
    -> hobby set('爬山','读书','游泳','烫头')  -- 集合
    -> );
# `Query OK, 0 rows affected (0.00 sec)`

mysql> insert into stu13 values ('tom','爬山');

mysql> insert into stu13 values ('Berry','爬山,游泳');
# `Query OK, 1 row affected (0.00 sec)`

mysql> insert into stu13 values ('Berry','游泳,爬山');   
# `Query OK, 1 row affected (0.00 sec)`

mysql> select * from stu13;
+-------+-----------+
| name  | hobby     |
+-------+-----------+
| tom   | 爬山       |
| Berry | 爬山,游泳   |
| Berry | 爬山,游泳   |
+-------+-----------+
# `3 rows in set (0.00 sec)`

MariaDB [stu]> insert into stu5 values('marry','摄影,读书');
# `Query OK, 1 row affected (0.014 sec)`

MariaDB [stu]> insert into stu5 values('tomy',8);
# `Query OK, 1 row affected (0.009 sec)`

MariaDB [stu]> insert into stu5 values('marry',16);
# `Query OK, 1 row affected, 1 warning (0.003 sec)`