Mysql数据类型

最新推荐文章于 2025-08-22 11:10:38 发布

转载最新推荐文章于 2025-08-22 11:10:38 发布 · 64 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/xiao-apple36/p/9539165.html

文章标签：

#数据库

数值类型

MySQL支持所有标准SQL数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。

注意：对于整型为该类型指定宽度时，仅仅只是指定查询结果的显示宽度，与存储范围无关，存储范围如下

类型	大小	范围（有符号）	范围（无符号）	用途
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 字节	(-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的值	小数值

整型使用：

========================================
        tinyint[(m)] [unsigned] [zerofill]

            小整数，数据类型用于保存一些范围的整数数值范围：
            有符号：
                -128 ～ 127
            无符号：
～ 255

            PS： MySQL中无布尔值，使用tinyint(1)构造。



========================================
        int[(m)][unsigned][zerofill]

            整数，数据类型用于保存一些范围的整数数值范围：
            有符号：
                    -2147483648 ～ 2147483647
            无符号：
～ 4294967295



========================================
        bigint[(m)][unsigned][zerofill]
            大整数，数据类型用于保存一些范围的整数数值范围：
            有符号：
                    -9223372036854775808 ～ 9223372036854775807
            无符号：
 ～  18446744073709551615

mysql> create table t2(x tinyint);
Query OK, 0 rows affected (0.01 sec)

mysql> desc t2;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| x     | tinyint(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into t2 values(-129),(-128),(127),(128);
ERROR 1264 (22003): Out of range value for column 'x' at row 1
mysql> 
mysql> insert into t2 values(-128),(127);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t2;
+------+
| x    |
+------+
| -128 |
|  127 |
+------+
2 rows in set (0.00 sec)

mysql> create table t3(x tinyint unsigned);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t3 values(255),(0),(-1);
ERROR 1264 (22003): Out of range value for column 'x' at row 3
mysql> 
mysql> insert into t3 values(255),(0);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t3;
+------+
| x    |
+------+
|  255 |
|    0 |
+------+
2 rows in set (0.00 sec)

mysql> create table t4(x int); #默认为有符号，即数字前有正负号
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t4 values(-2147483648),(2147483647);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t4;
+-------------+
| x           |
+-------------+
| -2147483648 |
|  2147483647 |
+-------------+
2 rows in set (0.00 sec)

mysql> insert into t4 values(-2147483648),(2147483649);
ERROR 1264 (22003): Out of range value for column 'x' at row 2
mysql> 
mysql> create table t5(x int(3) zerofill);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t5 values(1),(11),(111),(11111);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t5;
+-------+
| x     |
+-------+
|   001 |
|   011 |
|   111 |
| 11111 | #超过宽度限制仍然可以存
+-------+
4 rows in set (0.00 sec)

mysql> desc t5;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type                     | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| x     | int(3) unsigned zerofill | YES  |     | NULL    |       |
+-------+--------------------------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> desc t4;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| x     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

注意：为该类型指定宽度时，仅仅只是指定查询结果的显示宽度，与存储范围无关，存储范围如下

其实我们完全没必要为整数类型指定显示宽度，使用默认的就可以了

默认的显示宽度，都是在最大值的基础上加1

浮点型使用

======================================
#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能够存储精确值的原因在于其内部按照字符串存储。

mysql> create table t6(x float(256,31));
ERROR 1425 (42000): Too big scale 31 specified for column 'x'. Maximum is 30.
mysql> create table t6(x float(255,30));
Query OK, 0 rows affected (0.01 sec)

mysql> create table t7(x double(255,30));
Query OK, 0 rows affected (0.01 sec)

mysql> create table t8(x decimal(66,30));
ERROR 1426 (42000): Too-big precision 66 specified for 'x'. Maximum is 65.
mysql> create table t8(x decimal(65,30));
Query OK, 0 rows affected (0.01 sec)

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

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

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

mysql> ^Csert into t7 values(1.1111111111111111111111111111111);

^C
mysql> 
mysql> 
mysql> select * from t6; #随着小数的增多，精度开始不准确
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select * from t7; #精度比float要准确点，但随着小数的增多，同样变得不准确
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select * from t8; #精度始终准确,d为30，于是只留了30位小数
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111111111111111111111111111 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select length(x) from t8;
+-----------+
| length(x) |
+-----------+
|        32 |
+-----------+
1 row in set (0.00 sec)

　总结：　

　float：在位数比较短的情况下不精准（**** 数值越大，越不准确 ****）

　double：在位数比较长的情况下不精准（**** 数值越大，越不准确 ****）

　decimal：如果是小数，则推荐使用decimal

　　　因为精准，内部原理是以字符串的形式去存

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

表示时间值的日期和时间类型为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	混合日期和时间值，时间戳

有下面几种类型：

datatime:2017-09-06 10:30:22
data:2017-09-06
time:10:30:22
year:2017
timeatamp:和datatime是一样的，就是有时区的特性

create table t4(
id int primary key auto_increment,
born_year year,
born_date date,
born_dt datetime,
born_time time
);

insert into t4(born_year,born_date,born_dt,born_time) values(now(),now(),now(),now());
COMMIT;



select * from t4

字符串类型

字符串类型指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 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

char:简单粗暴，不够就用空格凑够固定长度存放起来，浪费空间，但是存储速度快
    (牺牲空间，提高速度)
varchar(你有几个就存几个):精准，计算出待存放数据的长度，节省空间，存取速度慢
        （牺牲速度，提高效率）

枚举与集合

字段的值只能在给定范围中选择，如单选框，多选框

enum枚举：规定一个范围：这个范围可以有多个，但是为该字段传值时，只能取规定范围内的其中一个
set集合：规定一个范围：这个范围可以有多个，但是为该字段传值时，可以取规定范围内的一个或多个
enum如果你不传值，默认是第一个值，或者为NUll

create table student(
id int primary key auto_increment,
name char(5),
sex enum('male','female'), #enum 代表枚举类型
hobbies set('eat','play','study','coding') #set 代表集合类型
);
insert into student(name,sex,hobbies) values('yy','none','eat');
COMMIT;
select * from student;  #如果设置了sex是枚举类型，就的从设定的里面选其中的一个存
insert into student(name,sex,hobbies) values('xx','female','play,study');
COMMIT;
select * from student;  #如果设置了hobbies是集合类型，就得从设定的里面选其中一个或者多个值来存