MySQL数据库的数据类型和基于MySQL数据类型的综合实例项目

文章目录

MySQL数据库的数据类型和基于MySQL数据类型的综合实例项目
1、MySQL具有的数据类型
2、如何选择数据类型
3、综合案例-MySQL数据库的数据类型的使用
4、总结

MySQL数据库的数据类型和基于MySQL数据类型的综合实例项目

1、MySQL具有的数据类型

1.1、MySQL整数类型

数值型数据类型主要用来存储数字。MySQL的整数类型如下所示
TINYINT、SMALLINT、MEDIUMINT、INT(INTEGER）、 BIGINT。

类型名称	说明	存储需求
TINYINT	很小的整数	1字节
SMALLINT	小的整数	2字节
MEDIUMINT	中等大小的整数	3字节
INT	普通大小的整数	4字节
BIGINT	大整数	8字节

创建表tmp1，其中字段x、y、z、m、n数据类型依次为TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，SQL语句如下：

mysql> CREATE TABLE tmp1 ( x TINYINT,  y SMALLINT,  z MEDIUMINT,  m INT,  n BIGINT );
Query OK, 0 rows affected (0.01 sec)

mysql> DESC tmp1;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| x     | tinyint   | YES  |     | NULL    |       |
| y     | smallint  | YES  |     | NULL    |       |
| z     | mediumint | YES  |     | NULL    |       |
| m     | int       | YES  |     | NULL    |       |
| n     | bigint    | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
5 rows in set (0.00 sec)

1.2、MySQL浮点类型和定点数

MySQL中使用浮点数和定点数来表示小数。
浮点类型：FLOAT、DOUBLE。
定点类型：DECIMAL。

类型名称	说明	存储需求
FLOAT	单精度浮点数	4字节
DOUBLE	双精度浮点数	8字节
DECIMAL(M,D)	定点数	M+2字节

DECIMAL的存储空间不是固定的，而是由M和D决定的。

创建表tmp2，其中字段x、y、z数据类型依次为FLOAT(5,1)、DOUBLE(5,1)和DECIMAL(5,1)，向表中插入数据5.12、5.15和5.123，SQL语句如下：

mysql> CREATE TABLE tmp2 ( x FLOAT(5,1),  y DOUBLE(5,1),  z DECIMAL(5,1) );
Query OK, 0 rows affected, 2 warnings (0.01 sec)

向tmp2表中插入数据：

mysql> INSERT INTO tmp2 VALUES(5.12, 5.15, 5.123);
Query OK, 1 row affected, 1 warning (0.01 sec)

查看警告信息

mysql> SHOW WARNINGS;
+-------+------+----------------------------------------+
| Level | Code | Message                                |
+-------+------+----------------------------------------+
| Note  | 1265 | Data truncated for column 'z' at row 1 |
+-------+------+----------------------------------------+
1 row in set (0.00 sec)

可以看到定点数被阶段了，发出警告
查看如下：

mysql> SELECT * FROM tmp2;
+------+------+------+
| x    | y    | z    |
+------+------+------+
|  5.1 |  5.2 |  5.1 |
+------+------+------+
1 row in set (0.00 sec)

1.3、MySQL日期与时间类型

MySQL中表示日期和时间的数据类型有

YEAR、TIME 、DATE、DATETIME和TIMESTAMP。

类型名称	日期格式	日期范围	存储需求
YEAR	YYYY	1901~2155	1字节
TIME	HH:MM:SS	-838:59:59~838:59:59	3字节
DATE	YYYY-MM-DD	1000-01-01~9999-12-3	3字节
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00~9999-12-31 23:59:59	8字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:1 UTC~2038-01-19 03:14:07 UTC	4字节

1、YEAR

存储格式：
存储方式(YEAR) 范围注意点
4位字符串或数字 ‘1901’~’2155’ 输入‘2022’或2022，插入到数据库的值都为2022

2位字符串	‘00’~’99’	 ‘00’~‘69’对应2000~2069；‘70’~‘99’对应1970~1999；
2位数字	1~99	1~69对应2001~2069；70~99对应1970~1999；0对应0000

创建数据表tmp3，定义数据类型为YEAR的字段y，向表中插入值2010，’2010’，’2166’，SQL语句如下：
首先创建表tmp3：

mysql> CREATE TABLE tmp3(  y YEAR );
Query OK, 0 rows affected (0.01 sec)

向表中插入数据：

mysql> INSERT INTO tmp3 values(2010),('2010');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

再次向表中插入数据：

mysql> INSERT INTO tmp3 values ('2166');
ERROR 1264 (22003): Out of range value for column 'y' at row 1

mysql> SHOW WARNINGS;
+-------+------+--------------------------------------------+
| Level | Code | Message                                    |
+-------+------+--------------------------------------------+
| Error | 1264 | Out of range value for column 'y' at row 1 |
+-------+------+--------------------------------------------+
1 row in set (0.00 sec)SELECT * FROM tmp3;

2166超过了最大取值：2155，所以会有Out of range value for column ‘y’ at row 1的错误。

查看数据表tmp3中的数据表

mysql> SELECT * FROM tmp3;
+------+
| y    |
+------+
| 2010 |
| 2010 |
+------+
2 rows in set (0.00 sec)

可以看到2010和‘2010’都转换成了2010。

向tmp3表中y字段插入2位字符串表示的YEAR值，分别为’0’、’00’、’77’和’10’，SQL语句如下：

向表中插入数据：

mysql> INSERT INTO tmp3 values('0'),('00'),('77'),('10');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

查看数据表tmp3中的数据表：

mysql> SELECT * FROM tmp3;
+------+
| y    |
+------+
| 2010 |
| 2010 |
| 2000 |
| 2000 |
| 1977 |
| 2010 |
+------+
6 rows in set (0.00 sec)

可以看到‘0’，‘00’转换成了2000，‘77’转换成了1977，‘10’转换成了2010。

向tmp3表中y字段插入2位数字表示表示的YEAR值，分别为0、75和13，SQL语句如下：
向表中插入数据：

mysql> INSERT INTO tmp3 values(0),(75),(13);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

查看数据表tmp3中的数据表：

SELECT * FROM tmp3;
mysql> SELECT * FROM tmp3;
+------+
| y    |
+------+
| 2010 |
| 2010 |
| 2000 |
| 2000 |
| 1977 |
| 2010 |
| 0000 |
| 1975 |
| 2013 |
+------+
9 rows in set (0.00 sec)

可以看到0转换成了0000，75转换成了1975，13转换成了2013。

2、TIME

存储格式：
存储方式(TIME) 范围注意点
‘HH:MM:SS -838:59:59~838:59:59 ‘HHMMSS’类型的字符串被理解为：‘HH:MM:SS’；’D HH:MM:SS’的D为0~34之间，为小时保存—》‘D*24+H’
创建数据表tmp4，定义数据类型为TIME的字段t，向表中插入值’10:05:05’，’23:23’，’2 10:10’，’3 02’，’10’，SQL语句如下：
首先创建表tmp4，

mysql> CREATE TABLE tmp4( t TIME );
Query OK, 0 rows affected (0.01 sec)

向表中插入数据：

INSERT INTO tmp4 values('10:05:05 '), ('23:23'), ('2 10:10'), ('3 02'),('10');
mysql> INSERT INTO tmp4 values('10:05:05 '), ('23:23'), ('2 10:10'), ('3 02'),('10');
Query OK, 5 rows affected, 1 warning (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 1

查看结果：

mysql> SELECT * FROM tmp4;
+----------+
| t        |
+----------+
| 10:05:05 |
| 23:23:00 |
| 58:10:00 |
| 74:00:00 |
| 00:00:10 |
+----------+
5 rows in set (0.00 sec)

向表tmp4中插入值’101112’，111213,’0’，107010，SQL语句如下：
向表中插入数据：

mysql> INSERT INTO tmp4 values('101112'),(111213),( '0');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

再向表中插入数据：

mysql> INSERT INTO tmp4 values ( 107010);
ERROR 1292 (22007): Incorrect time value: '107010' for column 't' at row 1
mysql> show warnings;
+-------+------+--------------------------------------------------------+
| Level | Code | Message                                                |
+-------+------+--------------------------------------------------------+
| Error | 1292 | Incorrect time value: '107010' for column 't' at row 1 |
+-------+------+--------------------------------------------------------+
1 row in set (0.00 sec)

可以看到插入的时间不对会报错。

3、DATE

DATE类型用在仅需要日期值时，没有时间部分，在存储时需要3字节。日期格式为’YYYY-MM-DD’—》YYYY表示年，MM表示月，DD表示日。在给Date类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合DATE的日期格式即可。
(1)、以‘YYYY-MM-DD’或者‘YYYYMMDD’字符串格式表示的日期，取值范围为“1000-01-01’~ 9999-12-3’。例如，输入‘2022-08-01’ 或者‘20220801’，期都为2022-08-01。
(2)、以‘YY-MM-DD’ 或者’YYMMDD’’ 字符串格式表示的日期，在这里YY表示两位的年值。Mysql使用以下规则解释两位年值：‘00_{69’范围的年值转换为‘2000} 2069’;‘70~ 99’范围的年值转换为‘1970~ 1999’。例如，输入‘22-08-01’，插入数据库的日期为2022-08-01; 输入‘991231’，插入数据的日期为1999-12-31。
(3)、以YY-MM-DD或者YYMMDD数字格式表示的日期，与前面相似，00_{69范围的年值转换为2000} 2069，70~ 99范围的年值转换为1970~1999。例如，输入12-12-31 插入数据库的日期为2012-12-31;输入981231，插入数据的日期为1998-12-31。
(4)、使用CURRENT DATE或者NOW(),插入当前系统日期。

创建数据表tmp5，定义数据类型为DATE的字段d，向表中插入“YYYY-MM-DD”和“YYYYMMDD”字符串格式日期，SQL语句如下：
首先创建表tmp5：

mysql> CREATE TABLE tmp5(d DATE);
Query OK, 0 rows affected (0.01 sec)

向表中插入“YYYY-MM-DD”和“YYYYMMDD”格式日期：

mysql> INSERT INTO tmp5 values('2022-08-01'),('19990108'),('20220610');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

查看插入结果：

mysql> SELECT * FROM tmp5;
+------------+
| d          |
+------------+
| 2022-08-01 |
| 1999-01-08 |
| 2022-06-10 |
+------------+
3 rows in set (0.00 sec)

向tmp5表中插入“YY-MM-DD”和“YYMMDD”字符串格式日期，SQL语句如下：
向表中插入“YY-MM-DD”和“YYMMDD”格式日期：

mysql> INSERT INTO tmp5 values ('99-09-09'),( '990909'), ('000101') ,('111111');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

查看插入结果：

SELECT * FROM tmp5;
mysql> SELECT * FROM tmp5;
+------------+
| d          |
+------------+
| 2022-08-01 |
| 1999-01-08 |
| 2022-06-10 |
| 1999-09-09 |
| 1999-09-09 |
| 2000-01-01 |
| 2011-11-11 |
+------------+
7 rows in set (0.00 sec)

向tmp5表中插入YY-MM-DD和YYMMDD数字格式日期，SQL语句如下：
向表中插入YY-MM-DD和YYMMDD数字格式日期：

mysql> INSERT INTO tmp5 values(990909), ( 000101) ,( 111111);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

查看插入结果：

SELECT * FROM tmp5;
mysql> SELECT * FROM tmp5;
+------------+
| d          |
+------------+
| 1999-09-09 |
| 2000-01-01 |
| 2011-11-11 |
+------------+
3 rows in set (0.00 sec)

向tmp5表中插入系统当前日期，SQL语句如下：
向表中插入系统当前日期：

mysql> INSERT INTO tmp5 values( CURRENT_DATE() ),( NOW() );
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 1

查看插入结果：

SELECT * FROM tmp5;
mysql> SELECT * FROM tmp5;
+------------+
| d          |
+------------+
| 1999-09-09 |
| 2000-01-01 |
| 2011-11-11 |
| 2022-08-05 |
| 2022-08-05 |
+------------+
5 rows in set (0.00 sec)

4、DATETIME

DATETIME类型用于需要同时包含日期和时间信息的值，在存储时需要8字节。日期格式为’YYYY-MM-DD HH:MM:SS’。其中，YYYY表示年，MM表示月，DD表示日，HH表示小时，MM表示分钟，SS表示秒。在给DATETIME类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合DATETIME的日期格式即可。

(1)、以’YYYY-MM-DD HH:MM:SS’ 或者’YYYYMMDDHHMMSS’字符串格式表示的值，取值范围为‘1000-01-0100:00:00’~‘ 9999-12-323:59:59’。例如，输入‘2019-12-31 05: 05: 05’或者20191231050505’ ，插入数据库的DATETIME值都为2019-12-31 05: 05: 05。
(2)、以’YY-MM-DD HH:MM:SS’ 或者‘YYMMDDHHMMSS’ 字符串格式表示的日期，在这里YY表示两位的年值。与前面相同，‘00~69’ 范围的年值转换为“2000~2069’ ，‘ 70_{99’范围的年值转换为‘1970}1999’。例如，输入‘19-12-31 05:05:05’，插入数据库的DATETIME为2019-12-31 05:05:05;输入‘990505050505’ ，插入数据库的DATETIME为1999-05-05:05:05:
(3)以YYYYMMDDHHMMSS或者YYMMDDHHMMSS数字格式表示的日期和时间。例如，输入20191231050505，插入数据库的DATETIME为2019-12-31 05:05:05;输入991231050505，插入数据的DATETIME为1999-12-31 05: 05:05。

创建数据表tmp6，定义数据类型为DATETIME的字段dt，向表中插入“YYYY-MM-DD HH:MM:SS”和“YYYYMMDDHHMMSS”字符串格式日期和时间值，SQL语句如下：
首先创建表tmp6：

mysql> CREATE TABLE tmp6(  dt DATETIME );
Query OK, 0 rows affected (0.01 sec)

向表中插入“YYYY-MM-DD HH:MM:SS”和“YYYYMMDDHHMMSS”格式日期：

mysql> INSERT INTO tmp6 values('1998-08-08 08:08:08'),('19980808080808'),('20101010101010');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

查看插入结果：

mysql> SELECT * FROM tmp6;
+---------------------+
| dt                  |
+---------------------+
| 1998-08-08 08:08:08 |
| 1998-08-08 08:08:08 |
| 2010-10-10 10:10:10 |
+---------------------+
3 rows in set (0.00 sec)

向tmp6表中插入“YY-MM-DD HH:MM:SS”和“YYMMDDHHMMSS”字符串格式日期和时间值，SQL语句如下：
向表中插入“YY-MM-DD HH:MM:SS”和“YYMMDDHHMMSS”格式日期：

mysql> INSERT INTO tmp6 values('98-12-09 09:09:09'),('981209090909'),('121010101010');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

查看插入结果：

mysql> SELECT * FROM tmp6;
+---------------------+
| dt                  |
+---------------------+
| 1998-08-08 08:08:08 |
| 1998-08-08 08:08:08 |
| 2010-10-10 10:10:10 |
| 1998-12-09 09:09:09 |
| 1998-12-09 09:09:09 |
| 2012-10-10 10:10:10 |
+---------------------+
6 rows in set (0.00 sec)

向tmp6表中插入YYYYMMDDHHMMSS 和YYMMDDHHMMSS数字格式日期和时间值，SQL语句如下：
向表中插入YYYYMMDDHHMMSS和YYMMDDHHMMSS数字格式日期和时间：

mysql> INSERT INTO tmp6 values(19951008060606), (101016105030);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

查看插入结果：

SELECT * FROM tmp6;
mysql> SELECT * FROM tmp6;
+---------------------+
| dt                  |
+---------------------+
| 1998-08-08 08:08:08 |
| 1998-08-08 08:08:08 |
| 2010-10-10 10:10:10 |
| 1998-12-09 09:09:09 |
| 1998-12-09 09:09:09 |
| 2012-10-10 10:10:10 |
| 1995-10-08 06:06:06 |
| 2010-10-16 10:50:30 |
+---------------------+
8 rows in set (0.00 sec)

向tmp6表中插入系统当前日期和时间值，SQL语句如下：
向表中插入系统当前日期：

mysql> INSERT INTO tmp6 values( NOW() );
Query OK, 1 row affected (0.01 sec)

查看插入结果：

SELECT * FROM tmp6;
mysql> SELECT * FROM tmp6;
+---------------------+
| dt                  |
+---------------------+
| 1998-08-08 08:08:08 |
| 1998-08-08 08:08:08 |
| 2010-10-10 10:10:10 |
| 1998-12-09 09:09:09 |
| 1998-12-09 09:09:09 |
| 2012-10-10 10:10:10 |
| 1995-10-08 06:06:06 |
| 2010-10-16 10:50:30 |
| 2022-08-05 15:15:39 |
+---------------------+
9 rows in set (0.00 sec)

5、TIMESTAMP

TIMESTAMP的显示格式与DATETIME相同，显示宽度固定在19个字符，日期格式为YYYY-MM-DD HH:MM:SS, 在存储时需要4字节。TIMESTAMP 列的取值范围小于DATETIME的取值范围：‘1970-01-01 00:00:01’ UTC~‘2038-01-19 03:14:07’UTC.其中，UTC (Coordinated
Universal Time)为世界标准时间，因此在插入数据时，要保证在合法的取值范围内。
创建数据表tmp7，定义数据类型为TIMESTAMP的字段ts，向表中插入值’19950101010101’，’950505050505’，’1996-02-02 02:02:02’，’97@03@03 03@03@03’，121212121212，NOW()，SQL语句如下：

mysql> CREATE TABLE tmp7( ts TIMESTAMP);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO tmp7 values ('19950101010101'),
    -> ('950505050505'),
    -> ('1996-02-02 02:02:02'),
    -> ('97@03@03 03@03@03'),
    -> (121212121212),
    -> ( NOW() );
Query OK, 6 rows affected, 1 warning (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 1

查看插入结果：

mysql> SELECT * FROM tmp7;
+---------------------+
| ts                  |
+---------------------+
| 1995-01-01 01:01:01 |
| 1995-05-05 05:05:05 |
| 1996-02-02 02:02:02 |
| 1997-03-03 03:03:03 |
| 2012-12-12 12:12:12 |
| 2022-08-05 15:18:34 |
+---------------------+
6 rows in set (0.00 sec)

1.4、MySQL字符串与文本类型

字符串类型用来存储字符串数据，除了可以存储字符串数据之外，还可以存储其它数据，比如图片和声音的二进制数据。
CHAR、VARCHAR、BINARY、VARBINARY
BLOB、TEXT、ENUM和SET。
MySQL支持两类字符型数据:文本字符串和二进制字符串。

文本字符串类型是指CHAR、VARCHAR、TEXT、ENUM和SET。

类型名称	说明	存储需求
CHAR(M)	固定长度非二进制字符串	M字节，1≤M≤255
VARCHAR(M)	变长非二进制字符串	L+1字节，在此L≤M和1≤M≤255
TINYTEXT	非常小的非二进制字符串	L+1字节，在此L<2^8
TEXT	小的非一进制字符串	L+2字节，在此L<2^16
MEDIUMTEXT	中等大小的非二进制字符串	L+3字节，在此L<2^24
LONGTEXT	大的非二进制字符串	L+4字节，在此L<2^32
ENUM	枚举类型，只能有一个枚举字符串值	1或2字节，取决于枚举值的数目最大值为65535
SET	一个设置，字符串对象可以有零个或多个SET成员	1、 2、3、4或8字节，取决于集合成员的数量，最多为64个成员

1、CHAR

CHAR(M)为固定长度字符串，在定义时指定字符串列长。当保存时在右侧填充空格，以达到指定的长度。M表示列长度，M的范围是0-255个字符。例如，CHAR(4)定义了一个固定长度的字符串列，其包含的字符个数最大为4。当检索到CHAR值时，尾部的空格将被删除。

2、VARCHAR

VARCHAR(M)是长度可变的字符串，M表示最大列长度。M的范围是0~-65535. VARCHAR的最大实际长度由最长的行的大小和使用的字符集确定,而其实际占用的空间为字符串的实际长度加1。例如，VARCHAR(50)定义了一个最大长度为 50的字符串，如果插入的字符串只有 10个字符，则实际存储的字符串为10个字符和一一个字符串结束字符。VARCHAR在值保存和检索时尾部的空格仍保留。

创建tmp8表，定义字段ch和vch数据类型依次为CHAR(4)、VARCHAR(4)向表中插入数据“ab ”，SQL语句如下：
创建表tmp8：

mysql> CREATE TABLE tmp8(
    -> ch  CHAR(4),
    -> vch  VARCHAR(4)
    -> );
Query OK, 0 rows affected (0.01 sec)

输入数据：

mysql> INSERT INTO tmp8 VALUES('ab  ', 'ab  ');
Query OK, 1 row affected (0.00 sec)

查询结果：

mysql> SELECT concat('(', ch, ')'), concat('(',vch,')') FROM tmp8;
+----------------------+---------------------+
| concat('(', ch, ')') | concat('(',vch,')') |
+----------------------+---------------------+
| (ab)                 | (ab  )              |
+----------------------+---------------------+
1 row in set (0.00 sec)

3、TEXT

TEXT列保存非二进制字符串，如文章内容、评论等。当保存或查询TEXT列的值时，不删除尾部空格。Text类型分为4种: TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT.不同的TEXT类型的存储空间和数据长度不同。

4、ENUM

ENUM是一个字符串对象，其值为表创建时在列规定中枚革的一列值。请法格式如下:
字段名ENUM(‘值1’，值2…值n’)
其中，“字段名” 指将要定义的字段，“值 n”指枚举列表中的第n个值。ENUM 类型的字段在取值时，只能在指定的枚举列表中取，而且一次只能取个。创建的成员中有空格时，其尾部的空格将自动被删除。ENUM值在内部用整数表示，并且每个枚举值均有一个索引值:列表值所允许的成员值从1开始编号，MySQL存储的就是这个索引编号。枚举最多可以有65535个元素。

创建表tmp9，定义ENUM类型的列enm(‘first’，‘second’，‘third’)，查看列成员的索引值，SQL语句如下：
首先，创建tmp9表：

mysql> CREATE TABLE tmp9( enm ENUM('first','second','third') );
Query OK, 0 rows affected (0.01 sec)

插入各个列值：

mysql> INSERT INTO tmp9 values('first'),('second') ,('third') , (NULL);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

查看索引值：

SELECT enm, enm+0 FROM tmp9;
mysql> SELECT enm, enm+0 FROM tmp9;
+--------+-------+
| enm    | enm+0 |
+--------+-------+
| first  |     1 |
| second |     2 |
| third  |     3 |
| NULL   |  NULL |
+--------+-------+
4 rows in set (0.00 sec)

创建表tmp10，定义INT类型的soc字段，ENUM类型的字段level，列表值为(‘excellent’,‘good’, ‘bad’)，向表tmp10中插入数据(70,‘good’)、(90,1)、(75,2)、(50,3) 、(100,‘best’)，SQL语句如下：
首先，创建数据表：

mysql> CREATE TABLE tmp10 (soc INT, level enum('excellent', 'good','bad') );
Query OK, 0 rows affected (0.01 sec)

插入数据：

mysql> INSERT INTO tmp10 values(70,'good'), (90,1),(75,2),(50,3);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

再次插入数据：

mysql> INSERT INTO tmp10 values (100,'best');
ERROR 1265 (01000): Data truncated for column 'level' at row 1

这里系统提示错误信息，可以看到，由于字符串值“best”不在ENUM列表中，所以对数据进行了阻止插入操作，查询结果如下：

mysql> SELECT * FROM tmp10;
+------+-----------+
| soc  | level     |
+------+-----------+
|   70 | good      |
|   90 | excellent |
|   75 | good      |
|   50 | bad       |
+------+-----------+
4 rows in set (0.00 sec)

5、SET

SET是一个字符串对象，可以有零或多个值。SET列最多可以有64个成员，其值为表创建时规定的一列值。指定包括多个SET成员的SET列值时，各成员之间用逗号()间隔开。语法格式如

SET('值1',值2'.....值n')

与ENUM类型相同，SET值在内部用整数表示，列表中每一个值都有一个索引编号。当创建表时，SET 成员值的尾部空格将自动被删除。与ENUM类型不同的是，ENUM类型的字段只能从定义的列值中选择一个值插入，而SET类型的列可从定义的列值中选择多个字符的联合。如果插入SET字段中列值有重复，则MySQL自动删除重复的值:插入SET字段的值的顺序并不重要，MySQL会在存入数据库时按照定义的顺序显示;如果插入了不正确的值，默认情况下，MySQL将忽视这些值，并给出警告。
创建表tmp11，定义SET类型的字段s，取值列表为(‘a’, ‘b’, ‘c’, ‘d’)，插入数据(‘a’)，(‘a,b,a’)，(‘c,a,d’)，(‘a,x,b,y’)，SQL语句如下：
首先创建表tmp11：

mysql> CREATE TABLE tmp11 ( s SET('a', 'b', 'c', 'd'));
Query OK, 0 rows affected (0.01 sec)

插入数据：

mysql> INSERT INTO tmp11 values('a'),( 'a,b,a'),('c,a,d');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

再次插入数据：

mysql> INSERT INTO tmp11 values ('a,x,b,y');
ERROR 1265 (01000): Data truncated for column 's' at row 1

由于插入了SET列不支持的值，因此MySQL给出错误提示。

查看结果：

mysql> SELECT * FROM tmp11;
+-------+
| s     |
+-------+
| a     |
| a,b   |
| a,c,d |
+-------+
3 rows in set (0.00 sec)

1.5、MySQL二进制类型

前面讲解了存储文本的字符串类型，这一节将讲解MySQL中存储二进制数据的数据类型
BIT、BINARY、VARBINARY、TINYBLOB、
BLOB、MEDIUMBLOB、LONGBLOB，

类型名称	说明	存储需求
BIT(M)	位字段类型	大约(M+7)/8字节
BINARY(M)	固定长度二进制字符串	M字节
VARBINARY(M)	可变长度二进制字符串	M+1字节
TINYBLOB(M)	非常小的BLOB	L+1字节，在此L<2^8
BLOB(M)	小BLOB	L+2字节，在此L<2^16
MEDIUMBLOB(M)	中等大小的BLOB	L+3字节，在此L<2^24
LONGBLOB(M)	非常大的BLOB	L+4字节，在此L<2^32

1、BIT类型

BIT类型是位字段类型。M表示每个值的位数，范围为1~64。如果M被省略，默认为1。如果为BIT(M)列分配的值的长度小于M位，
就在值的左边用0填充。例如，为BIT(6)列分配一个值b’101’,其效果与分配b0001相同。BIT 数据类型用来保存位字段值。例如，以二进制的形式保存数据13 (13的二进制形式为1101)，在这里需要位数至少为4位的BIT类型，即可以定义列类型为BIT(4),大于二进制1111的数据是不能插入BIT(4)类型的字段中的。

创建表tmp12，定义BIT(4)类型的字段b，向表中插入数据2、9、15、16。
首先创建表tmp12，SQL语句如下：

mysql> CREATE TABLE tmp12( b BIT(4) );
Query OK, 0 rows affected (0.01 sec)

插入数据：

mysql> INSERT INTO tmp12 VALUES(2), (9), (15);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

查询插入结果：

mysql> SELECT BIN(b+0) FROM tmp12;
+----------+
| BIN(b+0) |
+----------+
| 10       |
| 1001     |
| 1111     |
+----------+
3 rows in set (0.00 sec)

2、 BINARY和VARBINARY类型

BINARY和VARBINARY类型类似于CHAR和VARCHAR,不同的是它们包含二进制字节
字符串。其使用的语法格式如下:

列名称BINARY (M)或者VARBINARY (M)

BINARY类型的长度是固定的，指定长度之后，不足最大长度的，将在它们右边填充‘’齐以达到指定长度。例如:指定列数据类型为BINARY(3),当插入‘a’时，存储的内容实际为“a"，当插入“ab” 时，实际存储的内容为“ab”，不管存储的内容是否达到指定的长度，其存储空间

VARBINARY类型的长度是可变的，指定好长度之后，其长度可以在0到最大值之间。例如: 指定列数据类型为VARBINARY(20)，如果插入的值的长度只有10，则实际存储空间为10加1,即实际占用的空间为字符串的实际长度加1。

创建表tmp13，定义BINARY(3)类型的字段b和VARBINARY(3)类型的字段vb，并向表中插入数据’5’，比较两个字段的存储空间。
首先创建表tmp13，输入SQL语句如下：

mysql> CREATE TABLE tmp13(
    -> b binary(3),
    -> vb varbinary(30)
    -> );
Query OK, 0 rows affected (0.01 sec)

插入数据：

mysql> INSERT INTO tmp13 VALUES(5,5);
Query OK, 1 row affected (0.00 sec)

查看两个字段存储数据的长度：

mysql> SELECT length(b), length(vb) FROM tmp13;
+-----------+------------+
| length(b) | length(vb) |
+-----------+------------+
|         3 |          1 |
+-----------+------------+
1 row in set (0.00 sec)

mysql> SELECT b,vb,b = '5', b='5',vb='5',vb = '5' FROM tmp13;
+------------+------------+---------+-----------+--------+--------------+
| b          | vb         | b = '5' | b='5' | vb='5' | vb = '5' |
+------------+------------+---------+-----------+--------+--------------+
| 0x350000   | 0x35       |       0 |         1 |      1 |            0 |
+------------+------------+---------+-----------+--------+--------------+
1 row in set (0.00 sec)

3、BLOB类型

BLOB是一个二进制大对象，用来存储可变数量的数据。BLOB类型分为4种: TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB,它们可容纳值的最大长度不同，如下所示：

数据类型	存储范围
TINYBLOB	最大长度为255 (2^8-1) B
BLOB	最大长度为65535 (2^6-1) B
MEDIUMBLOB	最大长度为16777215 (2^24-1) B
LONGBLOB	最大长度为4294967295 (2^32 -1) B

BLOB列存储的是二进制字符串，
TEXT列存储的是非二进制字符串。
BLOB列没有字符集，并且排序和比较基于列值字节的数值;
TEXT列有一个字符集，并且根据字符集对值进行排序和比较。

2、如何选择数据类型

2.1、MySQL整数与浮点数的选择

如果不需要小数部分，就使用整数来保存数据:如果需要表示小数部分，就使用浮点数类型。对于浮点数据列，存入的数值会对该列定义的小数位进行四舍五入。例如，假设列的值的范围为1~99999,若使用整数，则MEDIUMINT UNSIGNED是最好的类型;若需要存储小数，则使用FLOAT类型。浮点类型包括FLOAT和DOUBLE类型。DOUBLE类型精度比FLOAT类型高，因此要求存储精度较高时应选择DOUBLE类型。

2.2、MySQL浮点数与定点数的选择

浮点数FLOAT、DOUBLE相对于定点数DECIMAL的优势是:在长度一定的情况下，浮点数能表示更大的数据范围。由于浮点数容易产生误差，因此对精确度要求比较高时，建议使用DECIMAL来存储。DECIMAL在MySQL中是以字符串存储的，用于定义货币等对精确度要求较高的数据。在数据迁移中，float(M,D)是非标准SQL定义，数据库迁移可能会出现问题，最好不要这样使用。另外，两个浮点数进行减法和比较运算时也容易出问题，因此在进行计算的时候，一定要小心。进行数值比较时，最好使用DECIMAL类型。

2.3、MySQL日期与时间类型的选择

MySQL对于不同种类的日期和时间有很多数据类型，比如YEAR和TIME。如果只需要记录年份，则使用YEAR类型即可;如果只记录时间，则使用TIME类型。如果同时需要记录日期和时间，则可以使用TIMESTAMP或者DATETIME类型。由于TIMESTAMP列的取值范围小于DATETIME 的取值范围，因此存储范围较大的日期最好使用
DATETIME.TIMESTAMP也有一个DATETIME不具备的属性。默认的情况下，当插入一条记录但并没有指定TIMESTAMP这个列值时，MySQL会把TIMESTAMP列设为当前的时间。因此当需要插入记录的同时插入当前时间时，使用TMESTAMP是方便的。另外，TIMESTAMP 在空间上比DATETIME更有效。

2.4、MySQL的CHAR和VARCHAR之间的选择

CHAR是固定长度字符，VARCHAR 是可变长度字符。CHAR 会自动删除插入数据的尾部空格，VARCHAR 不会删除尾部空格。CHAR是固定长度，所以它的处理速度比VARCHAR的速度要快，但是它的缺点是浪费存储存储引擎对于选择CHAR和VARCHAR的影响:对于MyISAM存储引擎:最好使用固定长度的数据列代替可变长度的数据列。这样可以使整个表静态化，从而使数据检索更快，用空间换时间。
对于InnoDB存储引擎:使用可变长度的数据列，因为InnoDB数据表的存储格式不分固定长度和可变长度，因此使用CHAR不一定比使用VARCHAR更好，但由于VARCHAR是按照实际的长度存储的，比较节省空间，所以对磁盘I/O和数据存储总量比较好。

2.5、MySQL的ENUM和SET的选择

ENUM只能取单值，它的数据列表是一个枚举集合。它的合法取值列表最多允许有65535个成员。因此，在需要从多个值中选取一个时，可以使用ENUM。比如:性别字段适合定义为ENUM类型，每次只能从‘男’或‘女’中取一个值。SET可取多值。它的合法取值列表最多允许有64个成员。空字符串也是一个合法的SET值。在需要取多个值的时候，适合使用SET类型，比如要存储一个人的兴趣爱好，最好使用SET类型。ENUM和SET的值是以字符串形式出现的，但在内部，MySQL 是以数值的形式存储它们的。

2.6、MySQL的BLOB和TEXT的选择

BLOB是二进制字符串，TEXT是非二进制字符串，两者均可存放大容量的信息。BLOB 主要
存储图片、音频信息等，而TEXT只能存储纯文本文件。

3、综合案例-MySQL数据库的数据类型的使用

1、案例要求

创建数据库myData，按照下面数据表格的要求创建数据表：myTable。
myTable表结构

字段名	数据类型	主键	外键	非空	唯一	自增
myInt	INT(10)	否	否	否	否	否
myFloat	FLOAT(5,2)	否	否	否	否	否
myTime	TIME	否	否	否	否	否
myChar	CHAR(10)	否	否	否	否	否
myBinary	binary(5)	否	否	否	否	否

通过MySQL数据库的数据类型的使用,来达到掌握有关MySQL数据库的数据类型使用的能力。其具体要求如下所示：
1、创建数据库myData
2、创建数据表myTable
3、向数据表myTable插入INT类型数据
4、向数据表myTable插入FLOAT类型数据
5、向数据表myTable插入TIME类型数据
6、向数据表myTable插入CHAR类型数据
7、向数据表myTable插入BINARY类型数据
8、查询数据表myTable的数据

2、案例实现过程

一、cmd环境运行MySQL语句

2.1、创建数据库myData

mysql> CREATE DATABASE myData;
Query OK, 1 row affected (0.00 sec)

2.2、创建数据表myTable

mysql> USE myData;
Database changed
mysql> CREATE TABLE myTable
    -> (
    -> myInt INT(10),
    -> myFloat FLOAT(5,2),
    -> myTime TIME,
    -> myChar CHAR(10),
    -> myBinary BINARY(5)
    -> );
Query OK, 0 rows affected, 2 warnings (0.01 sec)

2.3、向数据表myTable插入INT类型数据

向myInt字段插入数据

mysql> INSERT INTO myTable(myInt) VALUES(666);
Query OK, 1 row affected (0.00 sec)

查询myInt字段结果如下

mysql> SELECT myInt FROM myTable;
+-------+
| myInt |
+-------+
|   666 |
+-------+
1 row in set (0.00 sec)

2.4、向数据表myTable插入FLOAT类型数据

向myFloat字段插入数据

mysql> INSERT INTO myTable(myFloat) VALUES(6.23);
Query OK, 1 row affected (0.00 sec)

查询myFloat字段结果如下

mysql> SELECT myFloat FROM myTable;
+---------+
| myFloat |
+---------+
|    NULL |
|    6.23 |
+---------+
2 rows in set (0.00 sec)

2.5、向数据表myTable插入TIME类型数据

向myTime字段插入数据

mysql> INSERT INTO myTable(myTime) VALUES('10:24:36');
Query OK, 1 row affected (0.00 sec)

查询myTime字段结果如下

mysql> SELECT myTime FROM myTable;
+----------+
| myTime   |
+----------+
| NULL     |
| NULL     |
| 10:24:36 |
+----------+
3 rows in set (0.00 sec)

2.6、向数据表myTable插入CHAR类型数据

向myChar字段插入数据

mysql> INSERT INTO myTable(myChar) VALUES('CHAR');
Query OK, 1 row affected (0.00 sec)

查询myChar字段结果如下

mysql> SELECT myChar FROM myTable;
+--------+
| myChar |
+--------+
| NULL   |
| NULL   |
| NULL   |
| CHAR   |
+--------+
4 rows in set (0.00 sec)

2.7、向数据表myTable插入BINARY类型数据

向myBinary字段插入数据

mysql> INSERT INTO myTable(myBinary) VALUES(3);
Query OK, 1 row affected (0.00 sec)

查询myBinary字段结果如下

mysql> SELECT myBinary FROM myTable;
+--------------------+
| myBinary           |
+--------------------+
| NULL               |
| NULL               |
| NULL               |
| NULL               |
| 0x3300000000       |
+--------------------+
5 rows in set (0.00 sec)

2.8、查询数据表myTable的数据

查询myTable的数据结果如下

mysql> SELECT * FROM myTable;
+-------+---------+----------+--------+--------------------+
| myInt | myFloat | myTime   | myChar | myBinary           |
+-------+---------+----------+--------+--------------------+
|   666 |    NULL | NULL     | NULL   | NULL               |
|  NULL |    6.23 | NULL     | NULL   | NULL               |
|  NULL |    NULL | 10:24:36 | NULL   | NULL               |
|  NULL |    NULL | NULL     | CHAR   | NULL               |
|  NULL |    NULL | NULL     | NULL   | 0x3300000000       |
+-------+---------+----------+--------+--------------------+
5 rows in set (0.00 sec)

2.9、运行过程如下

1、登入数据库，创建数据库和数据表

在这里插入图片描述

2、向数据表中插入数据和查询数据

在这里插入图片描述

二、Navicat 软件环境运行MySQL语句

1、新建查询

进入Navicat软件好，新建一个创建数据库的查询。
在查询中编写如下的SQL语句，如下所示。


-- 1、创建数据库
CREATE DATABASE myData;

-- 2、创建数据表myTable
USE myData;

CREATE TABLE myTable
(
	myInt INT(10),
	myFloat FLOAT(5,2),
	myTime TIME,
	myChar CHAR(10),
	myBinary BINARY(5)
);

-- 3、向数据表myTable插入INT类型数据
-- 向myInt字段插入数据
INSERT INTO myTable(myInt) VALUES(666);

-- 查询myInt字段
SELECT myInt FROM myTable;

-- 4、向数据表myTable插入FLOAT类型数据
-- 向myFloat字段插入数据
INSERT INTO myTable(myFloat) VALUES(6.23);

-- 查询myFloat字段结
SELECT myFloat FROM myTable;


-- 5、向数据表myTable插入TIME类型数据
-- 向myTime字段插入数据
INSERT INTO myTable(myTime) VALUES('10:24:36');

-- 查询myTime字段
SELECT myTime FROM myTable;


-- 6、向数据表myTable插入CHAR类型数据
-- 向myChar字段插入数据
INSERT INTO myTable(myChar) VALUES('CHAR');

-- 查询myChar字段
SELECT myChar FROM myTable;


-- 7、向数据表myTable插入BINARY类型数据
-- 向myBinary字段插入数据
INSERT INTO myTable(myBinary) VALUES(3);

-- 查询myBinary字段
SELECT myBinary FROM myTable;

-- 8、查询数据表myTable的数据

SELECT * FROM myTable;

2、运行过程

运行结果如下所示：

1、创建数据库

在这里插入图片描述

2、创建数据表

在这里插入图片描述

3、插入和查询数据

在这里插入图片描述

4、总结

本文主要介绍了MySQL数据类型：
MySQL数据类型主要有整数类型、浮点类型、定点类型、日期和时间类型、字符串类型、文本类型和二进制类型。使读者可以很快掌握MySQL数据类型的基本使用方法。另外还介绍了分别使用cmd环境和Navicat软件环境中使用MySQL数据库的数据类型的方法。

文章目录