1. 数据类型
数据类型 | 常用类型 | 使用场景 |
---|---|---|
数字类型 | 整型/浮点型 | 薪资、年龄等 |
字符串 | 定长/变长字符串 | 用户名/密码、手机号、身份证号 评论、微博、帖子等 |
时间 | datetime/date | 注册时间、登录时间、修改时间、出生日期、跑步计时 等 |
枚举与集合 | 枚举/集合 | 性别 爱好 |
1.1 数字类型
类型 | 大小1 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
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,223,372,036,854,775,808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 极大整数值 |
FLOAT | 4字节 | (-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字节 | (-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的值 | 小数值 |
1)整型
整型包含:
tinyint
、smallint
、mediumint
、int
、bigint
注意:
对于整型来说,长度的约束都是无效的
,它能够表示的大小只和它存储的字节数相关
指定数据类型时,默认是有符号的
,如有需要,需单独指定该数据类型无符号
长度的约束,指的是字符位数
,而非多少字节
使用示例
# 创建一个t1表,定义三个字段均为tinyint类型,但是给予不同的约束
mysql> create table t1(age1 tinyint(2),age2 tinyint,age3 tinyint unsigned);
Query OK, 0 rows affected (0.01 sec)
mysql> desc t1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| age1 | tinyint(2) | YES | | NULL | |
| age2 | tinyint(4) | YES | | NULL | |
| age3 | tinyint(3) unsigned | YES | | NULL | |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into t1 values(255,255,255);
ERROR 1264 (22003): Out of range value for column 'age1' at row 1
#插入失败
# 此处报错,是因为age1 和age2默认都是带符号的,即其表示范围在(-128,127),而255超出了范围,所以报错
mysql> insert into t1 values(127,127,255);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+------+------+
| age1 | age2 | age3 |
+------+------+------+
| 127 | 127 | 255 |
+------+------+------+
1 row in set (0.00 sec)
# 插入成功,需做以下说说明
# 1. age1=127,是3位数字仍能成功插入,说明tinyint没有受到长度的约束
# 2. age2的显示宽度为4;因为age2默认是有符号的,范围在(-128,127),而-128算上符号长度为4位,所以age2显示宽度为4
# 3. age3的显示宽度为3;因为age3约束了是无符号,表示范围在(0,255),所以其宽度为3
2)浮点型
浮点型包含
float
单精度 、 double
双精度
a)float
语法
float(m,n)
m:表示一共有多少位
n :表示小数部分占其中多少位
使用示例
mysql> create table t2(salary float(5,2));
Query OK, 0 rows affected (0.01 sec)
mysql> desc t2;
+--------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| salary | float(5,2) | YES | | NULL | |
+--------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into t2 values(3333.123456789012345);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
# 插入失败
# 因为float(5,2)表示允许插入一个带两位小数带五位数;如果小数保留2位之后,整数部分就只剩3位,而本次插入带数据整数部分是4位带,所以报“out of range”错误
mysql> insert into t2 values(333.123456789012345) ;
Query OK, 1 row affected (0.00 sec)
mysql> select * from t2;
+--------+
| salary |
+--------+
| 333.12 |
+--------+
1 row in set (0.00 sec)
# 插入成功
# 本次插入数据带整数部分刚好有3位,符合数据的定义;
# 插入的数据按照约束,只保留2位小数
b)double
语法
double(m,n)
能够表示的小数点之后的位数更精准
c)float与double的对比
mysql> create table t3(salary1 float,salary2 double);
Query OK, 0 rows affected (0.01 sec)
mysql> desc t3;
+---------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------+------+-----+---------+-------+
| salary1 | float | YES | | NULL | |
| salary2 | double | YES | | NULL | |
+---------+--------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into t3 values(333.123956789012345,333.123956789012345);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t3;
+---------+--------------------+
| salary1 | salary2 |
+---------+--------------------+
| 333.124 | 333.12395678901237 |
+---------+--------------------+
1 row in set (0.00 sec)
# 由插入结果可以看出:
# 单精度到小数点3位的时候就已经四舍五入;而双精度则保留了更多的小数位
1.2 字符串类型
数据类型 | 大小 | 用途 |
---|---|---|
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类型类似,但是它们的保存和检索的方式不同。它们的最大长度(指字符长度)和是否尾部被保留等方面也不同。存储或检索过程中均不进行大小写转换。
BINARY和VARbINARY类似于CHAR和VARCHAR,不同的是他们包含二进制字符串而不要非二进制字符串。也就是说,他们包含字节字符串而不是字符字符串。这说明他们没有字符集,并且排序和比较基于列值字节的数值值。
BLOB是一个二进制大对象,可以容纳可变数量的数据。有4种BLOB类型:TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们区别在于可容纳存储范围不同。
TEXT类型有4种:TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。对应的4中BLOB类型,可存储的最大长度不同,可根据实际情况选择。
注意
char的长度固定为创建表时声明的长度,多余的部分补空格;范围是(0-255)
,即2**8
;定长存储
varchar的值是可变长字符串;范围是(0-65535)
,即2**16
;变长存储
存储内容 | 数据类型 | 存储方式 | 优缺点 |
---|---|---|---|
‘abc’ | char(8) | 'abc ’ | 节省时间,浪费空间 |
‘abc’ | varchar(255) | ‘abc’ | 存取都需要计算字串长度,因此有以下优缺点: 节省空间,浪费时间 |
考虑到实际场景中,空间对程序对限制较小(硬盘越来越大),因此尽量选择char来存储,以空间来换取时间(评论、帖子等可以使用varchar)
1)char 与 varchar
mysql> create table t4(username char(5),password varchar(8));
Query OK, 0 rows affected (0.00 sec)
mysql> desc t4;
+----------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| username | char(5) | YES | | NULL | |
| password | varchar(8) | YES | | NULL | |
+----------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into t4 values('administrator','admin');
ERROR 1406 (22001): Data too long for column 'username' at row 1
# 此报错是因为username字段插入的‘administrator’超出了限定的5个字符位
mysql> insert into t4 values('admin','administrator');
ERROR 1406 (22001): Data too long for column 'password' at row 1
# 此报错是因为password字段插入的‘administrator’超出限定的8个字符位
mysql> insert into t4 values('adm','admin');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t4 values('一二三四五','一二三四五六七八');
Query OK, 1 row affected (0.00 sec)
# 两个字段分别可以插入5个和8个中文汉子,说明长度约束指的是字符位数而不是字节位数
mysql> insert into t4 values('一二三四五六','一二三四五六七八');
ERROR 1406 (22001): Data too long for column 'username' at row 1
mysql> select * form t4;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'form t4' at line 1
mysql> select * from t4;
+-----------------+--------------------------+
| username | password |
+-----------------+--------------------------+
| adm | admin |
| 一二三四五 | 一二三四五六七八 |
+-----------------+--------------------------+
2 rows in set (0.00 sec)
mysql>
a)说明:
当数据库在"严格模式”下时,当插入的字符串不符合表中字段的长度约束时,就会插入失败并报错;
如果数据库是“非严格模式”的话,及时字符串长度超出字段的长度约束,也能插入,只不过超出的部分会被数据库截掉丢弃
通常更严谨的选择是“严格模式”
b)如何确认数据库是否为严格模式及如何设置严格模式
show variables like 'sql_mode';
若 value中包含“STRICT_TRANS_TABLES
”,即为严格模式
具体操作,见链接
1.3 日期和时间类型
表示时间值的日期和时间类型为DATA、TIME、YEAR、DATETIME和TIMESTAMP。
每个时间类型有一个有效值范围和一个“零”值,当指定不合法的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-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 | 混合日期和时间值,时间戳 |
常用的日期/时间数据类型:datetime
、date
、time
1)通过now()
写入当前时间
mysql> create table t5(dt datetime,d date,t time,ts timestamp,y year);
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> desc t5;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| dt | datetime | YES | | NULL | |
| d | date | YES | | NULL | |
| t | time | YES | | NULL | |
| ts | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| y | year(4) | YES | | NULL | |
+-------+-----------+------+-----+-------------------+-----------------------------+
5 rows in set (0.00 sec)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2019-10-01 14:29:31 |
+---------------------+
1 row in set (0.00 sec)
# now()函数可以获取当前时间(是随时间不断变化的)
mysql> insert into t5(dt) values (now());
Query OK, 1 row affected (0.00 sec)
mysql> select * from t5;
+---------------------+------+------+---------------------+------+
| dt | d | t | ts | y |
+---------------------+------+------+---------------------+------+
| 2019-10-01 14:30:04 | NULL | NULL | 2019-10-01 14:30:04 | NULL |
+---------------------+------+------+---------------------+------+
1 row in set (0.00 sec)
# 仅插入dt的时间后,ts也自动插入了时间;
# 每当这一行插入一个数据,ts这个字段就会自动把当前的时间插入
mysql>
# 以下分别插入 d、t、y
mysql> insert into t5(d) values (now());
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert into t5(t) values (now());
Query OK, 1 row affected (0.00 sec)
mysql> insert into t5(y) values (now());
Query OK, 1 row affected (0.01 sec)
mysql> select * from t5;
+---------------------+------------+----------+---------------------+------+
| dt | d | t | ts | y |
+---------------------+------------+----------+---------------------+------+
| 2019-10-01 14:30:04 | NULL | NULL | 2019-10-01 14:30:04 | NULL |
| NULL | 2019-10-01 | NULL | 2019-10-01 14:38:27 | NULL |
| NULL | NULL | 14:38:42 | 2019-10-01 14:38:42 | NULL |
| NULL | NULL | NULL | 2019-10-01 14:38:49 | 2019 |
+---------------------+------------+----------+---------------------+------+
4 rows in set (0.00 sec)
mysql>
关于timestamp扩展
a)每当像数据库里插入一条记录是,timestamp类型的时间字段就会自动插入当前的时间做记录
b)timestamp是从1970年开始计算,但是由于其大小仅4个字节,因此到2038年就结束了,该类型就无法再使用
c)
2)自定义写入时间
mysql> insert into t5(t) values ('110322');
Query OK, 1 row affected (0.01 sec)
# time格式会自动拆分时间格式
mysql> insert into t5(d) values ('20181102');
Query OK, 1 row affected (0.00 sec)
# date格式会自动识别时间格式
mysql> insert into t5(dt,d,t) values('2018-08-11 12:32:13','2018-0921','11:32:34');
ERROR 1292 (22007): Incorrect date value: '2018-0921' for column 'd' at row 1
# 分隔符需要明确一些,否则系统无法识别,报错
mysql> insert into t5(dt,d,t) values('2018-08-11 12:32:13','2018-09-21','11:32:34');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t5;
+---------------------+------------+----------+---------------------+------+
| dt | d | t | ts | y |
+---------------------+------------+----------+---------------------+------+
| 2019-10-01 14:30:04 | NULL | NULL | 2019-10-01 14:30:04 | NULL |
| NULL | 2019-10-01 | NULL | 2019-10-01 14:38:27 | NULL |
| NULL | NULL | 14:38:42 | 2019-10-01 14:38:42 | NULL |
| NULL | NULL | NULL | 2019-10-01 14:38:49 | 2019 |
| NULL | NULL | 11:03:22 | 2019-10-01 14:45:29 | NULL |
| NULL | 2018-11-02 | NULL | 2019-10-01 14:45:51 | NULL |
| 2018-08-11 12:32:13 | 2018-09-21 | 11:32:34 | 2019-10-01 14:48:32 | NULL |
+---------------------+------------+----------+---------------------+------+
7 rows in set (0.00 sec)
# 常见的分隔符系统是可以进行识别的
# 尽量使用常用的分隔符,如“-”,“/”,“:”等
mysql>
说明:
常见的分隔符系统是可以进行识别的
尽量使用常用的分隔符,如“-”,“/”,“:”
等;推荐date用“-”,time用“:”
3)关于timestamp的扩展
a)说明
每当像数据库里插入/修改一条记录时,timestamp类型的时间字段就会自动插入当前的时间做记录
timestamp是从1970年开始计算,但是由于其大小仅4个字节,因此到2038年就结束了,该类型就无法再使用
mysql> create table t6(dt datetime,d date,ts timestamp);
Query OK, 0 rows affected (0.02 sec)
mysql> desc t6;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| dt | datetime | YES | | NULL | |
| d | date | YES | | NULL | |
| ts | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
3 rows in set (0.00 sec)
mysql> insert into t6(d) values(now());
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from t6;
+------+------------+---------------------+
| dt | d | ts |
+------+------------+---------------------+
| NULL | 2019-10-01 | 2019-10-01 15:01:21 |
+------+------------+---------------------+
1 row in set (0.00 sec)
# 由以上结果可看出,虽然仅向d字段插入了数据,但是ts还是会把执行插入数据动作的时间记录下来
mysql> update t6 set dt='2018-08-08 11:12:13' where d='2019-10-01';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t6;
+---------------------+------------+---------------------+
| dt | d | ts |
+---------------------+------------+---------------------+
| 2018-08-08 11:12:13 | 2019-10-01 | 2019-10-01 15:02:15 |
+---------------------+------------+---------------------+
1 row in set (0.00 sec)
# 修改dt的时间之后,ts的时间也会更改,记录下最新一次修改本条数据的时间
mysql>
b)扩展
由于timestamp到2038年就无法使用了,但是timestamp自动记录动作时间的属性还是相当不错的,我们有没有其他方法去实现这种属性呢?
查看timestamp的属性信息
mysql> show create table t6;
+-------+--------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------+
| t6 | CREATE TABLE `t6` (
`dt` datetime DEFAULT NULL,
`d` date DEFAULT NULL,
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
由上面可知,timestamp的属性信息为:NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
那么,如果我们给其他字段(如dt)也加上此约束信息呢?
mysql> create table t7(dt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,d date,t time,ts timestamp,y year);
Query OK, 0 rows affected (0.02 sec)
mysql> desc t7;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| dt | datetime | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| d | date | YES | | NULL | |
| t | time | YES | | NULL | |
| ts | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| y | year(4) | YES | | NULL | |
+-------+-----------+------+-----+-------------------+-----------------------------+
5 rows in set (0.00 sec)
mysql> insert into t7(year) values('2017');
ERROR 1054 (42S22): Unknown column 'year' in 'field list'
mysql> insert into t7(y) values('2017');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t7;
+---------------------+------+------+---------------------+------+
| dt | d | t | ts | y |
+---------------------+------+------+---------------------+------+
| 2019-10-01 15:14:45 | NULL | NULL | 2019-10-01 15:14:45 | 2017 |
+---------------------+------+------+---------------------+------+
1 row in set (0.00 sec)
mysql> update t7 set d='2018-09-11' where y='2017';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t7;
+---------------------+------------+------+---------------------+------+
| dt | d | t | ts | y |
+---------------------+------------+------+---------------------+------+
| 2019-10-01 15:15:42 | 2018-09-11 | NULL | 2019-10-01 15:15:42 | 2017 |
+---------------------+------------+------+---------------------+------+
1 row in set (0.00 sec)
mysql>
由上面结果可知,此时dt字段已经完全拥有了和ts字段完全相同的属性了
1.4 枚举与集合
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个字节 | 多选:兴趣爱好 |
1)枚举与集合 的使用
mysql> create table t8(gender enum('男','女'), hobby set('唱歌','跳舞','rap'));
Query OK, 0 rows affected (0.01 sec)
mysql> desc t8;
+--------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------------------+------+-----+---------+-------+
| gender | enum('男','女') | YES | | NULL | |
| hobby | set('唱歌','跳舞','rap') | YES | | NULL | |
+--------+------------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into t8 values('男','唱歌');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t8;
+--------+--------+
| gender | hobby |
+--------+--------+
| 男 | 唱歌 |
+--------+--------+
1 row in set (0.00 sec)
mysql> insert into t8 values('不详','唱歌');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
# 报错原因:'不详' 不在gender字段的选项里面
mysql> insert into t8 values('男,女','唱歌');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
# 报错原因:gender是enum类型,只支持单选
mysql> insert into t8 values('女','唱歌,跳舞,rap');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t8;
+--------+-------------------+
| gender | hobby |
+--------+-------------------+
| 男 | 唱歌 |
| 女 | 唱歌,跳舞,rap |
+--------+-------------------+
2 rows in set (0.00 sec)
# 插入成功,说明:set支持多选
mysql> insert into t8 values('女','唱歌,跳舞,篮球');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1
# 报错原因:'篮球'不在hobby字段定义的集合范围内
mysql> insert into t8 values('女','唱歌,跳舞,唱歌');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t8;
+--------+-------------------+
| gender | hobby |
+--------+-------------------+
| 男 | 唱歌 |
| 女 | 唱歌,跳舞,rap |
| 女 | 唱歌,跳舞 |
+--------+-------------------+
3 rows in set (0.00 sec)
# 插入成功,虽然hobby字段插入了两个一样的'唱歌'爱好,但是set类型会自动去重再写入
mysql>