MySQL 支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。
1.数值类型
严格数值数据类型:INTEGER、SMALLINT、DECIMAL、NUMERIC
近似数值数据类型:FLOAT、REAL、DOUBLE PRECISION
无符号类型为在原类型后加上 unsigned。e.g. INT UNSIGNED
1⃣️整型数
//下给出一个表的结构:
+-------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| age | tinyint(3) unsigned | YES | | NULL | |
| salary| int(6) | YES | | NULL | |
+-------+----------------------+------+-----+---------+----------------+
//不难发现,以tinyint(3)为例,括号中的内容是当前字段的宽度,而非大小。tinyint大小为1Byte,宽度为3.
注意:在
desc cat
表的时候,数据类型后面有宽度的限制,宽度限制是可以超过的,但是不能超过所定义数据类型值的范围
2⃣️浮点数
//创建字段时的格式:
字段名 浮点类型(最大长度位m,小数位d)
注意:若输入的位数超过了设定的小数位则会四舍五入;存储范围取决于浮点类型及有无符号。
e.g.
//创建数据表:
mysql> create table t_1(
-> number_1 float(3,1),
-> number_2 double(5,2)
-> );
//表结构如下:
mysql> desc t_1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| number_1 | float(3,1) | YES | | NULL | |
| number_2 | double(5,2) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
//插入数据:
mysql> insert into t_1 values(2.9,2.77777777);
//表中数据如下:输入的位数超过了设定的小数位,进行了四舍五入。
mysql> select * from t_1;
+----------+----------+
| number_1 | number_2 |
+----------+----------+
| 2.9 | 2.78 |
+----------+----------+
2 rows in set (0.00 sec)
丢失精度:
以float为例
float
占用4Byte,其中3Byte用于表示有效数字,只能表示6-7 bit有效数字。
e.g.
//创建数据表:
mysql> create table t_2(
-> number double(20,19)
-> );
//表结构如下:
mysql> desc t_2;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| number | double(20,19)| YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
1 rows in set (0.00 sec)
//输入数据:
mysql> insert into t_2 values(9.1111111111111111111111111111111);
//查看表中数据:发现精度丢失,有效位数中小数位数比规定的小数位少!
mysql> select * from t_2;
+-----------------------+
| number |
+-----------------------+
| 9.1111111111111100000 |
+-----------------------+
1 row in set (0.00 sec)
因此,对于关键数据,一般不会要求使用浮点型,只在特殊要求下使用浮点类型。
3⃣️ 定点型
//创建字段时的格式:
字段名 定点类型(最大长度位m,小数位d)
定点数不会丢失精度,因为它的整数和小数分开存储管理,在生活中有许多的小数要求的精度非常之高,所以需要使用定点数。
//创建数据表:
mysql> create table t_3(
-> number decimal(20,19)
-> );
//表结构如下:
mysql> desc t_3;
+--------+----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------+------+-----+---------+-------+
| number | decimal(20,19) | YES | | NULL | |
+--------+----------------+------+-----+---------+-------+
1 row in set (0.00 sec)
//输入数据:
mysql> insert into t_3 values(8.111111111111111111111);
//查看表中数据:无精度丢失
mysql> select * from t_3;
+-----------------------+
| number |
+-----------------------+
| 8.1111111111111111111 |
+-----------------------+
1 row in set (0.00 sec)
2.字符串类型
应用中使用较多的为varchar(变长)类型、char(定长)类型。varchar类型会自动回收多余的字符空间,但相对于char的效率也会更低。
char(n) 和 varchar(n) 中括号中 n 代表字符的个数,并不代表字节个数,比如 CHAR(30) 就可以存储 30 个字符。
MySQL中提供专门存储文本的数据类型TEXT:TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT。对应的这 4 种 BLOB 类型,可存储的最大长度不同,可根据实际情况选择。
3.布尔类型 boolean
输入为true则对应的是
1
,false则
是0。
4.枚举类型 menu
//创建字段时的格式:
字段名 enum(选项1,选项2,......)
只能够输入枚举类型中存在的数据值,否则会报错。
枚举类型在计算机底层的管理方式:按照创建时的数据,对其进行编号:第一个选项为1,第二个为2......此方式极大的节约了存储空间。
因此,在输入数据时,枚举类型字段对应的数据可以直接输入数字1/2/3....
5.set类型
和menu类似,但set类型可以选择多个选项。
//创建字段时的格式:
字段名 set(选项1,选项2,......)
//在输入数据时,若想要输入多个选项:
insert into 表名 values('选项1,选项2,选项3...');
//错误写法如下:
insert into 表名 values('选项1','选项2','选项3',...);
//此写法是错误的!字段和字段间用,分割,而此处是在一个字段中选择多个选项!
e.g.
//表结构如下:
mysql> desc t_6;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| hobby | set('A','B','C','D','E') | YES | | NULL | |
+-------+--------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
//插入数据:
mysql> insert into t_6 values('A,B,C');
//查看表中数据:
mysql> select * from t_6;
+-------+
| hobby |
+-------+
| A,B,C |
+-------+
1 row in set (0.00 sec)
在存储时set使用2的n次方来存储,即若有n个数据,则有2^n种组合。
6.日期/时间类型
实际应用中多使用DATETIME类型。
每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP时间戳类型有专有的自动更新特性,有其特殊的应用,将在后续描述。
//创建字段的格式:
字段名 datetime/...
//在输入数据时应严格按照当前时间类型对应的格式输入,例如datetime类型:
insert into 表名 values('2021-09-17 01:28:00');