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表。
常用的数据类型有：int， float， decimal
整数部分：
mysql> create table t5 (id1 int(4),id2 int); Query OK, 0 rows affected (0.16 sec) mysql> desc t5; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id1 | int(4) | YES | | NULL | | | id2 | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> insert into t5 values (123,123); Query OK, 1 row affected (0.03 sec) mysql> select * from t5; +------+------+ | id1 | id2 | +------+------+ | 123 | 123 | +------+------+ 1 row in set (0.00 sec) mysql> insert into t5 values (12345,12345); Query OK, 1 row affected (0.03 sec) mysql> select * from t5; +-------+-------+ | id1 | id2 | +-------+-------+ | 123 | 123 | | 12345 | 12345 | +-------+-------+ 2 rows in set (0.00 sec) mysql> insert into t5 values (123,2147483648); Query OK, 1 row affected, 1 warning (0.02 sec) mysql> select * from t5; +-------+------------+ | id1 | id2 | +-------+------------+ | 123 | 123 | | 12345 | 12345 | | 123 | 2147483647 | +-------+------------+ 3 rows in set (0.00 sec) 范围测试 有符号和无符号 mysql> create table t6 (id1 int(4),id2 int unsigned); Query OK, 0 rows affected (0.25 sec) mysql> insert into t6 values (123,2147483648); Query OK, 1 row affected (0.03 sec) mysql> select * from t6; +------+------------+ | id1 | id2 | +------+------------+ | 123 | 2147483648 | +------+------------+ 1 row in set (0.00 sec)
注意：为该类型指定宽度时，仅仅只是指定查询结果的显示宽度，与存储范围无关，存储范围如下
其实我们完全没必要为整数类型指定显示宽度，使用默认的就可以了
默认的显示宽度，都是在最大值的基础上加1
小数部分：
长度约束测试 mysql> create table t7 (f float(10,3),d double(10,3),d2 decimal(10,3)); Query OK, 0 rows affected (0.20 sec) mysql> insert into t7 values (1.23456789,2.34567,3.56789); Query OK, 1 row affected, 1 warning (0.02 sec) mysql> select * from t7; +-------+-------+-------+ | f | d | d2 | +-------+-------+-------+ | 1.235 | 2.346 | 3.568 | +-------+-------+-------+ 1 row in set (0.00 sec)
#FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
定义：
单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30
有符号：
-3.402823466E+38 to -1.175494351E-38,
1.175494351E-38 to 3.402823466E+38
无符号：
1.175494351E-38 to 3.402823466E+38
精确度：
**** 随着小数的增多，精度变得不准确 ****
======================================
#DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
定义：
双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30
有符号：
-1.7976931348623157E+308 to -2.2250738585072014E-308
2.2250738585072014E-308 to 1.7976931348623157E+308
无符号：
2.2250738585072014E-308 to 1.7976931348623157E+308
精确度：
****随着小数的增多，精度比float要高，但也会变得不准确 ****
======================================
decimal[(m[,d])] [unsigned] [zerofill]
定义：
准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。
精确度：
**** 随着小数的增多，精度始终准确 ****
对于精确数值计算时需要用此类型
decaimal能够存储精确值的原因在于其内部按照字符串存储。
总结：
float：在位数比较短的情况下不精准（**** 数值越大，越不准确 ****）
double：在位数比较长的情况下不精准（**** 数值越大，越不准确 ****）
decimal：如果是小数，则推荐使用decimal
因为精准，内部原理是以字符串的形式去存
日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性，将在后面描述。
常用:
date 描述年月日
datetime 描述年月日时分秒
mysql> create table t9 (d date,t time,y year,dt datetime,ts timestamp); Query OK, 0 rows affected (0.18 sec) mysql> desc t9; +-------+-----------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+-------------------+-----------------------------+ | d | date | YES | | NULL | | | t | time | YES | | NULL | | | y | year(4) | YES | | NULL | | | dt | datetime | YES | | NULL | | | ts | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +-------+-----------+------+-----+-------------------+-----------------------------+ 5 rows in set (0.01 sec) mysql> insert into t9 values (null,null,null,null,null); Query OK, 1 row affected (0.03 sec) mysql> select * from t9; +------+------+------+------+---------------------+ | d | t | y | dt | ts | +------+------+------+------+---------------------+ | NULL | NULL | NULL | NULL | 2018-09-29 11:27:29 | +------+------+------+------+---------------------+ 1 row in set (0.00 sec) mysql> insert into t9 values(now(),now(),now(),now(),now()); Query OK, 1 row affected, 1 warning (0.03 sec) 每种数据类型表示的时间格式 mysql> select * from t9; +------------+----------+------+---------------------+---------------------+ | d | t | y | dt | ts | +------------+----------+------+---------------------+---------------------+ | NULL | NULL | NULL | NULL | 2018-09-29 11:27:29 | | 2018-09-29 | 11:29:07 | 2018 | 2018-09-29 11:29:07 | 2018-09-29 11:29:07 | +------------+----------+------+---------------------+---------------------+ 2 rows in set (0.00 sec) datetime 和 timestamp的范围控制 mysql> insert into t9 (dt) values (10010101000000); Query OK, 1 row affected (0.03 sec) mysql> select * from t9; +------------+----------+------+---------------------+---------------------+ | d | t | y | dt | ts | +------------+----------+------+---------------------+---------------------+ | NULL | NULL | NULL | NULL | 2018-09-29 11:27:29 | | 2018-09-29 | 11:29:07 | 2018 | 2018-09-29 11:29:07 | 2018-09-29 11:29:07 | | NULL | NULL | NULL | 1001-01-01 00:00:00 | 2018-09-29 11:30:33 | +------------+----------+------+---------------------+---------------------+ 3 rows in set (0.00 sec) mysql> mysql> insert into t9 (ts) values (10010101000000); Query OK, 1 row affected, 1 warning (0.03 sec) mysql> select * from t9; +------------+----------+------+---------------------+---------------------+ | d | t | y | dt | ts | +------------+----------+------+---------------------+---------------------+ | NULL | NULL | NULL | NULL | 2018-09-29 11:27:29 | | 2018-09-29 | 11:29:07 | 2018 | 2018-09-29 11:29:07 | 2018-09-29 11:29:07 | | NULL | NULL | NULL | 1001-01-01 00:00:00 | 2018-09-29 11:30:33 | | NULL | NULL | NULL | NULL | 0000-00-00 00:00:00 | +------------+----------+------+---------------------+---------------------+ 4 rows in set (0.00 sec)
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) date/time/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) datetime示例
字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
CHAR列的长度固定为创建表是声明的长度,范围(0-255);而VARCHAR的值是可变长字符串范围(0-65535)。
CHAR 0-255字节 定长字符串
定长 浪费磁盘 存取速度非常快
VARCHAR 0-65535 字节 变长字符串
变长 节省磁盘空间 存取速度相对慢
char(5) ' abc' 5 'abcde' 5
这一列数据的长度变化小 手机号 身份证号 学号
频繁存取、对效率要求高
短数据
varchar(5) '3abc' 4 '5abcde' 6
这一列的数据长度变化大 name 描述信息
对效率要求相对小
相对长
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) char/varchar示例
ENUM和SET类型
ENUM中文名称叫枚举类型，它的值范围需要在创建表时通过枚举方式显示。ENUM只允许从值集合中选取单个值，而不能一次取多个值。
SET和ENUM非常相似，也是一个字符串对象，里面可以包含0-64个成员。根据成员的不同，存储上也有所不同。set类型可以允许值集合中任意选择1或多个元素进行组合。对超出范围的内容将不允许注入，而对重复的值将进行自动去重。
枚举 enum 单选 集合 set 多选
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 ('nezha','male'); Query OK, 1 row affected (0.00 sec) # 不能同时插入'male,female'两个值，也不能插入不属于'male,female'的值 mysql> insert into t10 values ('nezha','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 ('yuan','烫头,喝酒,烫头'); Query OK, 1 row affected (0.01 sec) mysql> select * from t11; +------+---------------+ | name | hobby | +------+---------------+ | yuan | 喝酒,烫头 | +------+---------------+ row in set (0.00 sec) # 不能选择不属于set('抽烟','喝酒','烫头','翻车')中的项， mysql> insert into t11 values ('alex','烫头,翻车,看妹子'); ERROR 1265 (01000): Data truncated for column 'hobby' at row 1 set/enum示例
