mysql字段类型
[TOC]
整型类型
- 合理选择bit、int、tinyint、decimal等数字类型
- int使用固定4个字节存储，int(11)与int(4)只是显示宽度的区别
- 建议使用 UNSIGNED 存储非负整数，可存储更大的数字
| 字段类型 |
存储空间 |
取值范围(signed) |
最大值(signed) |
取值范围(unsigned) |
最大值(unsigned) |
| bit |
1 bytes |
0 或 1 |
- |
0 或 1 |
- |
| tinyint |
1 bytes |
-2^7 ~ 2^7-1 |
127 |
0 ~ 2^8-1 |
255 |
| smallint |
2 bytes |
-2^15 ~ 2^15-1 |
32767</font> |
0 ~ 2^16-1 |
65535 |
| mediumint |
3 bytes |
-2^23 ~ 2^15-1 |
8388607 |
0 ~ 2^24-1 |
16777215 |
| int |
4 bytes |
-2^31 ~ 2^31-1 |
2147483647 |
0 ~ 2^32-1 |
4294967295 |
| bigint |
8 bytes |
-2^63 ~ 2^63-1 |
9223372036854775807 |
0 ~ 2^64-1 |
18446744073709551615 |
浮点类型
- CPU处理float的速度比处理double快，消耗内存和占用空间也小；若值很大或很小则 float 变得不精确
- 浮点类型的 xxx 中 (m,d) 的含义差不多，m是小数点前后的数字个数(定义长度)，d是小数点后面的数字个数(定义精度)，比如 decimal(5,2) 可以存储 -999.99 ~ 999.99
- 对于精度比较高的数据，比如 money 建议使用 decimal，不要考虑float/double
| 字段类型 |
存储空间 |
取值范围 |
用途 |
| float |
4 bytes |
- |
单精度浮点数值 |
| double |
8 bytes |
- |
双精度浮点数值 |
| decimal(m,d) |
m+2 bytes |
依赖m和d的值,1<m<65,0<d<30 |
数字型 |
字符串类型
- mysql的字符串类型分为：文本字符串、二进制字符串
- char(n) 中的 n 代表最大可容纳的字符的个数，并不代表字节个数
| 字段类型 |
存储空间 |
取值范围 |
说明 |
| char(n) |
- |
1 <= n <= 65535 |
固定长度的字符串，存放字符 |
| varchar(n) |
- |
1 <= n <= 65535 |
可变长度的字符串 |
| tinytext |
- |
- |
非常小的文本字符串 |
| text |
- |
- |
小的文本字符串，存放纯文本文件 |
| mediumtext |
- |
- |
中等大小的文本字符串 |
| longtext |
- |
- |
大的文本字符串 |
| binary(n) |
- |
- |
固定长度的二进制字符串 |
| varbinary(n) |
- |
- |
可变长度的二进制字符串 |
| tinyblob(n) |
- |
- |
非常小的blob |
| blob(n) |
- |
- |
小的blob，存放图片、音频等 |
| mediumblob(n) |
- |
- |
中等大小的blob |
| longblob(n) |
- |
- |
非常大的blob |
#.使用longblob或者mediumblob保存视频文件
CREATE TABLE test (id int, name varchar(20), movie longblob);
INSERT INTO test VALUES(1, 'titanic', LOAD_FILE("/tmp/titanic.mp4"));
#.blob转varchar
SELECT id,log,CONVERT(log USING utf8mb4) AS log2,CAST(log AS CHAR(10000) CHARACTER SET utf8mb4) AS log3 FROM run_logs LIMIT 10;
时间类型
- 精度方面，MySQL 的 timestamp(3) = SQLServer 的 datetime，比如
2021-12-31 23:59:09.856
- MySQL 5.7 之前，timestamp 默认值是
CURRENT_TIMESTAMP，并且随着记录更新而更新
- MySQL 5.7 之前，datetime 支持
DEFAULT CURRENT_TIMESTAMP，但不支持 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
- 导出的时候，timestamp以utc时间格式导出，导入则自动由UTC格式转为系统默认时区，如果需要看到和导入与实际相符的时间戳，需要加入参数--tz-utc=false用于禁止timestamp时区转换，默认是开启的
| 字段类型 |
存储空间 |
日期格式 |
日期范围 |
默认值 |
| year |
1 bytes |
YYYY |
1901 ~ 2155 |
- |
| time |
3 bytes |
HH:MM:SS |
-838:59:59 ~ 838:59:59 |
- |
| date |
3 bytes |
YYYY-MM-DD |
1000-01-01 ~ 9999-12-31 |
- |
| datetime |
8 bytes |
YYYY-MM-DD HH:MM:SS |
1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 |
CURRENT_TIMESTAMP,与时区无关 |
| timestamp |
4 bytes |
YYYY-MM-DD HH:MM:SS |
1970-01-01 00:00:01 ~ 2038-01-19 |
- |
mysql> show create table timetest;
+
| Table | Create Table |
+
| timetest | CREATE TABLE `timetest` (
`id` int(11),
`date1` date DEFAULT '1000-01-01',
`date2` datetime DEFAULT CURRENT_TIMESTAMP,
`date3` timestamp DEFAULT CURRENT_TIMESTAMP,
`date4` timestamp(3) DEFAULT CURRENT_TIMESTAMP(3),
`time5` time DEFAULT '00:00:00',
`year6` year(4) DEFAULT '1901'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
mysql> INSERT INTO timetest(id) VALUES(1);
mysql> SELECT * FROM timetest;
+
| id | date1 | date2 | date3 | date4 | time5 | year6 |
+
| 1 | 1000-01-01 | 2021-12-12 09:59:09 | 2021-12-12 09:59:09 | 2021-12-12 09:59:09.856 | 00:00:00 | 1901 |
+