MySQL数据类型
注: 转载 https://www.runoob.com/mysql/mysql-data-types.html
MySQL中定义数据字段的类型对数据库的优化是非常有效的
MySQL支持多种类型, 大致可以分为三类: 数值, 日期/时间和字符串(字符)类型
注: 本文全部使用小写形式, SQL语句不区分大小写命令
- 数值类型
MySQL 支持所有标准SQL数值数据类型
这些类型包括严格数值数据类型(integer, smallint, decimal和numeric) , 以及浮点型(float, real, 和 double,precision )
关键字int是integer的同义词,关键字dec是decimal的同义词。
bit数据类型保存位字段值,并且支持mylsam、memory、InnoDB和BDB表。
作为SQL标准的扩展,MySQL也支持整数类型tinyint、mediumint和bigint。下面的表显示了需要的每个整数类型的存储和范围。
- 整数类型表
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
tinyint | 1 byte | (-128,127) | (0,255) | 小整数值 |
smallint | 2 bytes | (-32 768,32 767) | (0,65 535) | 大整数值 |
mediumint | 3 bytes | (-8 388 608,8 388 607) | (0,16 777 215) | 大整数值 |
int或integer | 4 bytes | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 大整数值 |
bigint | 8 bytes | (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 极大整数值 |
例: 如果超过类型规定数值报错是SQLmode设置了严格模式, 修改xxx即可
无符号tinyint
mysql> create table t2(x tinyint unsigned);
mysql> insert into t2 values(-1,0,255,256);
mysql> select * from t2;
+------+
| x |
+------+
| 0 | -1存成了0
| 0 | 无符号,最小值为0
| 255 | 无符号,最大值为255
| 255 | 256存成了255
+------+
有符号int(默认)
mysql> create table t3(x int); #默认为有符号整数
mysql> insert into t3 values
-> (-2147483649),
-> (-2147483648),
-> (2147483647),
-> (2147483648);
mysql> select * from t3;
+-------------+
| x |
+-------------+
| -2147483648 | #-2147483649存成了-2147483648
| -2147483648 | #有符号,最小值为-2147483648
| 2147483647 | #有符号,最大值为2147483647
| 2147483647 | #2147483648存成了2147483647
+-------------+
无符号int
mysql> create table t4(x int unsigned);
mysql> insert into t4 values(-1,0,4294967295,4294967296);
mysql> select * from t4;
+------------+
| x |
+------------+
| 0 | #-1存成了0
| 0 | #无符号,最小值为0
| 4294967295 | #无符号,最大值为4294967295
| 4294967295 | #4294967296存成了4294967295
+------------+
mysql> bigint/zerofill类型使用都一样
zerofill为限制长度
- 浮点型表
精度问题
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
float | 4 bytes | (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) | 0,(1.175 494 351 E-38,3.402 823 466 E+38) | 单精度 浮点数值 |
double | 8 bytes | (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
decimal | 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
例:
-------------------------------------float----------------------------------
float[(M,D)] [UNSIGNED] [ZEROFILL]
单精度浮点数(非准确小数值),m是数字总个数,d是小数点后个数。m最大值为255,d最大值为30
精确度: 随着小数的增多,精度变得不准确
mysql> create table t1(x float(256,31));
ERROR 1425 (42000): Too big scale 31 specified for column 'x'. Maximum is 30.
mysql> create table t1(x float(256,30));
ERROR 1439 (42000): Display width out of range for column 'x' (max = 255)
mysql> create table t1(x float(255,30)); #建表成功
mysql> insert into t1 values(1.1111111111111111111111111111111); #小数点后31个1
mysql> select * from t1; #随着小数的增多,精度开始不准确
+----------------------------------+
| x |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+
---------------------------------double-------------------------------------
double[(M,D)] [UNSIGNED] [ZEROFILL]
双精度浮点数(非准确小数值),m是数字总个数,d是小数点后个数。m最大值为255,d最大值为30
随着小数的增多,精度比float要高,但也会变得不准确
mysql> create table t2(x double(255,30)); #建表成功
mysql> insert into t2 values(1.1111111111111111111111111111111);
mysql> select * from t2; #精度比float要准确点,但随着小数的增多,同样变得不准确
+----------------------------------+
| x |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+
-------------------------------decimal--------------------------------------
decimal[(m[,d])] [unsigned] [zerofill]
准确的小数值,m是数字总个数(负号不算),d是小数点后个数。 m最大值为65,d最大值为30。
随着小数的增多,精度始终准确
对于精确数值计算时需要用此类型
decaimal能够存储精确值的原因在于其内部按照字符串存储。
mysql> create table t3(x decimal(65,30)); #建表成功
mysql> insert into t3 values(1.1111111111111111111111111111111);
mysql> select * from t3; #精度始终准确,d为30,于是只留了30位小数
+----------------------------------+
| x |
+----------------------------------+
| 1.111111111111111111111111111111 |
+----------------------------------+
- 日期/时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性,将在后面描述。
类型 | 大小 ( bytes) | 范围 | 格式 | 用途 |
---|---|---|---|---|
date | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
time | 3 | ‘-838:59:59’/‘838:59:59’ | HH:MM:SS | 时间值或持续时间 |
year | 1 | 1901/2155 | YYYY | 年份值 |
datetime | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
timestamp | 4 | 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值,时间戳 |
例:
---------------------------------year--------------------------------------
mysql> create table t1(born_year year); #无论year指定何种宽度,最后都默认是year(4)
mysql> insert into t1 values(1000,1234,2020,2025);
mysql> select * from t1;
+-----------+
| born_year |
+-----------+
| 0000 |
| 1901 |
| 2155 |
| 0000 |
+-----------+
-----------------------date,time,datetime-----------------------------------
mysql> create table t1(d date,t time,dt datetime);
mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d | date | YES | | NULL | |
| t | time | YES | | NULL | |
| dt | datetime | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
mysql> insert into t1 values(now(),now(),now()); now()函数为当前时间, 包括年
mysql> select * from t1;
+------------+----------+---------------------+
| d | t | dt |
+------------+----------+---------------------+
| 2020-09-25 | 16:26:54 | 2020-09-25 16:26:54 |
+------------+----------+---------------------+
----------------------------timestamp-------------------------------------
mysql> create table t1(time timestamp);
mysql> insert into t1 values();
mysql> insert into t1 values(null);
mysql> select * from t1;
+---------------------+
| time |
+---------------------+
| 2017-07-25 16:29:17 |
| 2017-07-25 16:30:01 |
+---------------------+
注意:
- 单独插入时间时,需要以字符串的形式,按照对应的格式插入
- 插入年份时,尽量使用4位值
timestamp和datetime区别
在实际应用的很多场景中,MySQL的这两种日期类型都能够满足我们的需要,存储精度都为秒,但在某些情况下,会展现出他们各自的优劣。下面就来总结一下两种日期类型的区别。
1.DATETIME的日期范围是1001——9999年,TIMESTAMP的时间范围是1970——2038年。
2.DATETIME存储时间与时区无关,TIMESTAMP存储时间与时区有关,显示的值也依赖于时区。在mysql服务器,操作系统以及客户端连接都有时区的设置。
3.DATETIME使用8字节的存储空间,TIMESTAMP的存储空间为4字节。因此,TIMESTAMP比DATETIME的空间利用率更高。
4.DATETIME的默认值为null;TIMESTAMP的字段默认不为空(not null),默认值为当前时间(CURRENT_TIMESTAMP),如果不做特殊处理,并且update语句中没有指定该列的更新值,则默认更新为当前时间。
- 字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
类型 | 大小 | 用途 |
---|---|---|
char | 0-255 bytes | 定长字符串 |
varchar | 0-65535 bytes | 变长字符串 |
tinyblob | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
tinytext | 0-255 bytes | 短文本字符串 |
blob | 0-65 535 bytes | 二进制形式的长文本数据 |
text | 0-65 535 bytes | 长文本数据 |
mediumblob | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
mediumtext | 0-16 777 215 bytes | 中等长度文本数据 |
longblob | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
longtext | 0-4 294 967 295 bytes | 极大文本数据 |
char(n) 和 varchar(n) 中括号中 n 代表字符的个数,并不代表字节个数,比如 CHAR(30) 就可以存储 30 个字符。
例: 常用char/varchar
- char类型:定长,简单粗暴,浪费空间,存取速度快
mysql> CREATE TABLE t1 (a char(10),b varchar(10);
insert into t1 values('哈哈哈 ','哈哈哈 ');
mysql> SET sql_mode='';
mysql> select x,char_length(x),y,char_length(y) from t1;
+-----------+----------------+------------+----------------+
| x | char_length(x) | y | char_length(y) |
+-----------+----------------+------------+----------------+
| 哈哈哈 | 3 | 哈哈哈 | 4 |
+-----------+----------------+------------+----------------+
略施小计,让char现出原形
mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
这下子char原形毕露了......
mysql> select x,char_length(x),y,char_length(y) from t1;
+-------------+----------------+------------+----------------+
| x | char_length(x) | y | char_length(y) |
+-------------+----------------+------------+----------------+
| 哈哈哈 | 5 | 哈哈哈 | 4 |
+-------------+----------------+------------+----------------+
- varchar类型:变长,精准,节省空间,存取速度慢
CREATE TABLE t ( a CHAR(10), b VARCHAR(10));
...
枚举类型/集合类型
字段的值只能在给定范围中选择,如单选框,多选框
enum 单选 只能在给定的范围内选一个值,如性别 sex 男male/女female
set 多选 在给定的范围内可以选择一个或一个以上的值(爱好1,爱好2,爱好3…)
mysql> create table consumer(
-> name varchar(50),
-> sex enum('male','female'),
-> level enum('vip1','vip2','vip3','vip4','vip5'), #在指定范围内,多选一
-> hobby set('play','music','read','study') #在指定范围内,多选多
-> );
mysql> insert into consumer values("chen",'male','vip5','read,study');
mysql> select * from consumer;
+------+--------+-------+------------+
| name | sex | level | hobby |
+------+--------+-------+------------+
| chen | male | vip5 | read,study |
+------+--------+-------+------------+