时间：2021-07-01 10:21:17 帮助过：7人阅读
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的值 | 小数值 |
# 创建表一个是默认宽度的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 | +------+------+ 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 | +------------+--------+ 2 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 | | +-------+------------------+------+-----+---------+-------+ 2 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 1int整数示例
# 创建表的三个字段分别为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 | +------+------+------+ 1 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 | +------+------+------+ 2 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 | +------+------+------+ 3 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 | +-------+-------+------+ 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 | +---------+--------------------+------+ 2 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-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 | 混合日期和时间值，时间戳 |
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 | | +-------+----------+------+-----+---------+-------+ 3 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 | +------------+----------+---------------------+ 1 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 | +------------+----------+---------------------+ 2 rows in set (0.00 sec)date/time/datetime示例
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 | +-------+-----------+------+-----+-------------------+-----------------------------+ 1 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 | +---------------------+ 1 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 1 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 1 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 | +---------------------+---------------------+ 2 rows in set (0.00 sec)timestamp示例
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 | +-------+-----------+------+-----+-------------------+-----------------------------+ 1 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 | +---------------------+ 1 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>timestamp示例2
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 | +------+ 1 row in set (0.00 sec)year示例
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-0
