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

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~19992位数字	1~99	1~69对应2001~206970~99对应1970~19990对应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使用以下规则解释两位年值:‘0069’范围的年值转换为‘2000 2069’;‘70~ 99’范围的年值转换为‘1970~ 1999’。例如,输入‘22-08-01’,插入数据库的日期为2022-08-01; 输入‘991231’,插入数据的日期为1999-12-31。
(3)、以YY-MM-DD或者YYMMDD数字格式表示的日期,与前面相似,0069范围的年值转换为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’ ,‘ 7099’范围的年值转换为‘19701999’。例如,输入‘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数据库的数据类型的方法。