MySQL支持的数据类型

时间:2022-03-23 04:01:11

MySQL支持的数据类型

一、数值类型

MySQL支持所有标准SQL数值数据类型。这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词。MySQL支持的整数类型有TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

对于小数的表示,MySQL分为两种方式:浮点数和定点数。浮点数包括float(单精度)和double(双精度),而定点数只有decimal一种,在MySQL中以字符串的形式存放,比浮点数更精确,适合用来表示货币等精度高的数据。BIT数据类型保存位字段值,并且支持MyISAM、MEMORY、InnoDB和BDB表。

类型 大小 范围(有符号) 范围(无符号)unsigned约束 用途
TINYINT 1 字节 (-128,127) (0,255) 小整数值
SMALLINT 2 字节 (-32 768,32 767) (0,65 535) 大整数值
MEDIUMINT 3 字节 (-8 388 608,8 388 607) (0,16 777 215) 大整数值
INT或INTEGER 4 字节 (-2 147 483 648,2 147 483 647) (0,4 294 967 295) 大整数值
BIGINT 8 字节 (-9 233 372 036 854 775 808,9 223 372 036 854 775 807) (0,18 446 744 073 709 551 615) 极大整数值
FLOAT 4 字节 float(255,30) (-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 字节 double(255,30) (-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 double(65,30) 依赖于M和D的值 依赖于M和D的值 小数值

1.1 int整数示例

# 创建表一个是默认宽度的int,一个是指定宽度的int(5)

mysql> create table t1 (id1 int,id2 int(5));
Query OK, 0 rows affected (0.02 sec)

# 像t1中插入数据1,1
mysql> insert into t1 values (1,1);
Query OK, 1 row affected (0.01 sec)

# 可以看出结果上并没有异常
mysql> select * from t1;
 ------ ------ 
| id1  | id2  |
 ------ ------ 
|    1 |    1 |
 ------ ------ 
row in set (0.00 sec)

# 那么当我们插入了比宽度更大的值,会不会发生报错呢?
mysql> insert into t1 values (111111,111111);
Query OK, 1 row affected (0.00 sec)

# 答案是否定的,id2仍然显示了正确的数值,没有受到宽度限制的影响
mysql> select * from t1;
 ------------ -------- 
| id1        | id2    |
 ------------ -------- 
| 0000000001 |  00001 |
| 0000111111 | 111111 |
 ------------ -------- 
rows in set (0.00 sec)

# 修改id1字段 给字段添加一个unsigned表示无符号
mysql> alter table t1 modify id1 int unsigned;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
 ------- ------------------ ------ ----- --------- ------- 
| Field | Type             | Null | Key | Default | Extra |
 ------- ------------------ ------ ----- --------- ------- 
| id1   | int(10) unsigned | YES  |     | NULL    |       |
| id2   | int(5)           | YES  |     | NULL    |       |
 ------- ------------------ ------ ----- --------- ------- 
rows in set (0.01 sec)

# 当给id1添加的数据大于214748364时,可以顺利插入
mysql> insert into t1 values (2147483648,2147483647);
Query OK, 1 row affected (0.00 sec)

# 当给id2添加的数据大于214748364时,会报错
mysql> insert into t1 values (2147483647,2147483648);
ERROR 1264 (22003): Out of range value for column 'id2' at row 1

1.2 小数示例

# 创建表的三个字段分别为float,double和decimal参数表示一共显示5位,小数部分占2位
mysql> create table t2 (id1 float(5,2),id2 double(5,2),id3 decimal(5,2));
Query OK, 0 rows affected (0.02 sec)

# 向表中插入1.23,结果正常
mysql> insert into t2 values (1.23,1.23,1.23);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
 ------ ------ ------ 
| id1  | id2  | id3  |
 ------ ------ ------ 
| 1.23 | 1.23 | 1.23 |
 ------ ------ ------ 
row in set (0.00 sec)

# 向表中插入1.234,会发现4都被截断了
mysql> insert into t2 values (1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t2;
 ------ ------ ------ 
| id1  | id2  | id3  |
 ------ ------ ------ 
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
 ------ ------ ------ 
rows in set (0.00 sec)

# 向表中插入1.235发现数据虽然被截断,但是遵循了四舍五入的规则
mysql> insert into t2 values (1.235,1.235,1.235);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t2;
 ------ ------ ------ 
| id1  | id2  | id3  |
 ------ ------ ------ 
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
| 1.24 | 1.24 | 1.24 |
 ------ ------ ------ 
rows in set (0.00 sec)

# 建新表去掉参数约束
mysql> create table t3 (id1 float,id2 double,id3 decimal);
Query OK, 0 rows affected (0.02 sec)

# 分别插入1.234
mysql> insert into t3 values (1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.00 sec)

# 发现decimal默认值是(10,0)的整数
mysql> select * from t3;
 ------- ------- ------ 
| id1   | id2   | id3  |
 ------- ------- ------ 
| 1.234 | 1.234 |    1 |
 ------- ------- ------ 
row in set (0.00 sec)

# 当对小数位没有约束的时候,输入超长的小数,会发现float和double的区别
mysql> insert into t3 values (1.2355555555555555555,1.2355555555555555555,1.2355555555555555555555);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t3;
 --------- -------------------- ------ 
| id1     | id2                | id3  |
 --------- -------------------- ------ 
|   1.234 |              1.234 |    1 |
| 1.23556 | 1.2355555555555555 |    1 |
 --------- -------------------- ------ 
rows in set (0.00 sec)

二、日期时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。TIMESTAMP类型有专有的自动更新特性,将在后面描述。

类型 大小 (字节) 范围 格式 用途
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-DDHH: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<br />HHMMSS 混合日期和时间值,时间戳

2.1 date/time/datetime示例

mysql> create table t4 (d date,t time,dt datetime);
Query OK, 0 rows affected (0.02 sec)
mysql> desc t4;
 ------- ---------- ------ ----- --------- ------- 
| Field | Type     | Null | Key | Default | Extra |
 ------- ---------- ------ ----- --------- ------- 
| d     | date     | YES  |     | NULL    |       |
| t     | time     | YES  |     | NULL    |       |
| dt    | datetime | YES  |     | NULL    |       |
 ------- ---------- ------ ----- --------- ------- 
rows in set (0.01 sec)

mysql> insert into t4 values (now(),now(),now());
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t4;
 ------------ ---------- --------------------- 
| d          | t        | dt                  |
 ------------ ---------- --------------------- 
| 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 |
 ------------ ---------- --------------------- 
row in set (0.00 sec)

mysql> insert into t4 values (null,null,null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t4;
 ------------ ---------- --------------------- 
| d          | t        | dt                  |
 ------------ ---------- --------------------- 
| 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 |
| NULL       | NULL     | NULL                |
 ------------ ---------- --------------------- 
rows in set (0.00 sec)

2.2 timestamp示例

mysql> create table t5 (id1 timestamp);
Query OK, 0 rows affected (0.02 sec)

mysql> desc t5;
 ------- ----------- ------ ----- ------------------- ----------------------------- 
| Field | Type      | Null | Key | Default           | Extra                       |
 ------- ----------- ------ ----- ------------------- ----------------------------- 
| id1   | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
 ------- ----------- ------ ----- ------------------- ----------------------------- 
row in set (0.00 sec)

# 插入数据null,会自动插入当前时间的时间
mysql> insert into t5 values (null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t5;
 --------------------- 
| id1                 |
 --------------------- 
| 2018-09-21 14:56:50 |
 --------------------- 
row in set (0.00 sec)

#添加一列 默认值是'0000-00-00 00:00:00'
mysql> alter table t5 add id2 timestamp;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t5 G;
*************************** 1. row ***************************
       Table: t5
Create Table: CREATE TABLE `t5` (
  `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `id2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
row in set (0.00 sec)

ERROR: 
No query specified

# 手动修改新的列默认值为当前时间
mysql> alter table t5 modify id2 timestamp default current_timestamp;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t5 G;
*************************** 1. row ***************************
       Table: t5
Create Table: CREATE TABLE `t5` (
  `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `id2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8
row in set (0.00 sec)

ERROR: 
No query specified

mysql> insert into t5 values (null,null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t5;
 --------------------- --------------------- 
| id1                 | id2                 |
 --------------------- --------------------- 
| 2018-09-21 14:56:50 | 0000-00-00 00:00:00 |
| 2018-09-21 14:59:31 | 2018-09-21 14:59:31 |
 --------------------- --------------------- 
rows in set (0.00 sec)

2.3 timestamp示例2

mysql> create table t6 (t1 timestamp);
Query OK, 0 rows affected (0.02 sec)

mysql> desc t6;
 ------- ----------- ------ ----- ------------------- ----------------------------- 
| Field | Type      | Null | Key | Default           | Extra                       |
 ------- ----------- ------ ----- ------------------- ----------------------------- 
| t1    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
 ------- ----------- ------ ----- ------------------- ----------------------------- 
row in set (0.01 sec)

mysql> insert into t6 values (19700101080001);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t6;
 --------------------- 
| t1                  |
 --------------------- 
| 1970-01-01 08:00:01 |
 --------------------- 
row in set (0.00 sec)
# timestamp时间的下限是19700101080001
mysql> insert into t6 values (19700101080000);
ERROR 1292 (22007): Incorrect datetime value: '19700101080000' for column 't1' at row 1

mysql> insert into t6 values ('2038-01-19 11:14:07');
Query OK, 1 row affected (0.00 sec)
# timestamp时间的上限是2038-01-19 11:14:07
mysql> insert into t6 values ('2038-01-19 11:14:08');
ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 11:14:08' for column 't1' at row 1
mysql> 

2.4 year示例

mysql> create table t7 (y year);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t7 values (2018);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t7;
 ------ 
| y    |
 ------ 
| 2018 |
 ------ 
row in set (0.00 sec)

2.5 datetime示例

mysql> create table t8 (dt datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t8 values ('2018-9-26 12:20:10');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t8 values ('2018/9/26 12 20 10');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t8 values ('20180926122010');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t8 values (20180926122010);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t8;
 --------------------- 
| dt                  |
 --------------------- 
| 2018-09-26 12:20:10 |
| 2018-09-26 12:20:10 |
| 2018-09-26 12:20:10 |
| 2018-09-26 12:20:10 |
 --------------------- 
rows in set (0.00 sec)

三、字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
| 类型 | 大小 | 用途 |
| :--------- | :------------------ | :------------------------------ |
| CHAR | 0-255字节 | 定长字符串 |
| VARCHAR | 0-65535 字节 | 变长字符串 |
| TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255字节 | 短文本字符串 |
| BLOB | 0-65 535字节 | 二进制形式的长文本数据 |
| TEXT | 0-65 535字节 | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295字节 | 极大文本数据 |

CHAR 和 VARCHAR 类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
CHAR列的长度固定为创建表是声明的长度,范围(0-255);而VARCHAR的值是可变长字符串范围(0-65535)。

3.1 char/varchar示例

mysql> create table t9 (v varchar(4),c char(4));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t9 values ('ab  ','ab  ');
Query OK, 1 row affected (0.00 sec)

# 在检索的时候char数据类型会去掉空格
mysql> select * from t9;
 ------ ------ 
| v    | c    |
 ------ ------ 
| ab   | ab   |
 ------ ------ 
row in set (0.00 sec)

# 来看看对查询结果计算的长度
mysql> select length(v),length(c) from t9;
 ----------- ----------- 
| length(v) | length(c) |
 ----------- ----------- 
|         4 |         2 |
 ----------- ----------- 
row in set (0.00 sec)

# 给结果拼上一个加号会更清楚
mysql> select concat(v,' '),concat(c,' ') from t9;
 --------------- --------------- 
| concat(v,' ') | concat(c,' ') |
 --------------- --------------- 
| ab            | ab            |
 --------------- --------------- 
row in set (0.00 sec)

# 当存储的长度超出定义的长度,会截断
mysql> insert into t9 values ('abcd  ','abcd  ');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t9;
 ------ ------ 
| v    | c    |
 ------ ------ 
| ab   | ab   |
| abcd | abcd |
 ------ ------ 
rows in set (0.00 sec)

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含字节字符串而不是字符字符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值值。
BLOB 是一个二进制大对象,可以容纳可变数量的数据。有 4 种 BLOB 类型:TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
有 4 种 TEXT 类型:TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型,可存储的最大长度不同,可根据实际情况选择。

四、ENUM和SET类型

ENUM中文名称叫枚举类型,它的值范围需要在创建表时通过枚举方式显示。ENUM只允许从值集合中选取单个值,而不能一次取多个值。
SET和ENUM非常相似,也是一个字符串对象,里面可以包含0-64个成员。根据成员的不同,存储上也有所不同。set类型可以允许值集合中任意选择1或多个元素进行组合。对超出范围的内容将不允许注入,而对重复的值将进行自动去重。

类型 大小 用途
ENUM 对1-255个成员的枚举需要1个字节存储; 对于255-65535个成员,
需要2个字节存储; 最多允许65535个成员。
单选:选择性别
SET 1-8个成员的集合,占1个字节 9-16个成员的集合,
占2个字节 17-24个成员的集合,占3个字节 25-32个成员的集合,
占4个字节 33-64个成员的集合,占8个字节
多选:兴趣爱好

4.1 set/enum示例

mysql> create table t10 (name char(20),gender enum('female','male'));
Query OK, 0 rows affected (0.01 sec)

# 选择enum('female','male')中的一项作为gender的值,可以正常插入
mysql> insert into t10 values ('nick','male');
Query OK, 1 row affected (0.00 sec)

# 不能同时插入'male,female'两个值,也不能插入不属于'male,female'的值
mysql> insert into t10 values ('nick','male,female');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1

mysql> create table t11 (name char(20),hobby set('抽烟','喝酒','烫头','翻车'));
Query OK, 0 rows affected (0.01 sec)

# 可以任意选择set('抽烟','喝酒','烫头','翻车')中的项,并自带去重功能
mysql> insert into t11 values ('tank','烫头,喝酒,烫头');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t11;
 ------ --------------- 
| name | hobby        |
 ------ --------------- 
| tank | 喝酒,烫头     |
 ------ --------------- 
row in set (0.00 sec)

# 不能选择不属于set('抽烟','喝酒','烫头','翻车')中的项,
mysql> insert into t11 values ('jason','烫头,翻车,看妹子');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1