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>CREATETABLE tmp1 ( x TINYINT,  y SMALLINT,  z MEDIUMINT,  m INT,  n BIGINT);
Query OK,0rows 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||+-------+-----------+------+-----+---------+-------+5rowsinset(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>CREATETABLE tmp2 ( x FLOAT(5,1),  y DOUBLE(5,1),  z DECIMAL(5,1));
Query OK,0rows affected,2warnings(0.01 sec)

向tmp2表中插入数据：

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

查看警告信息

mysql>SHOWWARNINGS;+-------+------+----------------------------------------+|Level| Code | Message                                |+-------+------+----------------------------------------+| Note  |1265|Data truncated forcolumn'z' at row1|+-------+------+----------------------------------------+1rowinset(0.00 sec)

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

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

1.3、MySQL日期与时间类型

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

YEAR、TIME 、DATE、DATETIME和TIMESTAMP。

类型名称日期格式日期范围存储需求YEARYYYY1901~~21551字节TIMEHH:MM:SS-838:59:59~~838:59:593字节DATEYYYY-MM-DD1000-01-01~~9999-12-33字节DATETIMEYYYY-MM-DD HH:MM:SS1000-01-01 00:00:00~~9999-12-31 23:59:598字节TIMESTAMPYYYY-MM-DD HH:MM:SS1970-01-01 00:00:1 UTC~2038-01-19 03:14:07 UTC4字节

1、YEAR

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

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

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

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

向表中插入数据：

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

再次向表中插入数据：

mysql>INSERTINTO tmp3 values('2166');
ERROR 1264(22003): Outof range valueforcolumn'y' at row1

mysql>SHOWWARNINGS;+-------+------+--------------------------------------------+|Level| Code | Message                                    |+-------+------+--------------------------------------------+| Error |1264|Outof range valueforcolumn'y' at row1|+-------+------+--------------------------------------------+1rowinset(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|+------+2rowsinset(0.00 sec)

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

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

向表中插入数据：

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

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

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

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

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

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

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

SELECT*FROM tmp3;
mysql>SELECT*FROM tmp3;+------+| y    |+------+|2010||2010||2000||2000||1977||2010||0000||1975||2013|+------+9rowsinset(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>CREATETABLE tmp4( t TIME);
Query OK,0rows affected (0.01 sec)

向表中插入数据：

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

查看结果：

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

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

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

再向表中插入数据：

mysql>INSERTINTO tmp4 values(107010);
ERROR 1292(22007): Incorrect timevalue: '107010'forcolumn't' at row1
mysql>showwarnings;+-------+------+--------------------------------------------------------+|Level| Code | Message                                                |+-------+------+--------------------------------------------------------+| Error |1292| Incorrect timevalue: '107010'forcolumn't' at row1|+-------+------+--------------------------------------------------------+1rowinset(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>CREATETABLE tmp5(d DATE);
Query OK,0rows affected (0.01 sec)

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

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

查看插入结果：

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

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

mysql>INSERTINTO tmp5 values('99-09-09'),('990909'),('000101'),('111111');
Query OK,4rows affected (0.00 sec)
Records: 4  Duplicates: 0Warnings: 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|+------------+7rowsinset(0.00 sec)

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

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

查看插入结果：

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

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

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

查看插入结果：

SELECT*FROM tmp5;
mysql>SELECT*FROM tmp5;+------------+| d          |+------------+|1999-09-09||2000-01-01||2011-11-11||2022-08-05||2022-08-05|+------------+5rowsinset(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表示两位的年值。与前面相同，‘0069’ 范围的年值转换为“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>CREATETABLE tmp6(  dt DATETIME);
Query OK,0rows affected (0.01 sec)

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

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

查看插入结果：

mysql>SELECT*FROM tmp6;+---------------------+| dt                  |+---------------------+|1998-08-0808:08:08||1998-08-0808:08:08||2010-10-1010:10:10|+---------------------+3rowsinset(0.00 sec)

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

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

查看插入结果：

mysql>SELECT*FROM tmp6;+---------------------+| dt                  |+---------------------+|1998-08-0808:08:08||1998-08-0808:08:08||2010-10-1010:10:10||1998-12-0909:09:09||1998-12-0909:09:09||2012-10-1010:10:10|+---------------------+6rowsinset(0.00 sec)

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

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

查看插入结果：

SELECT*FROM tmp6;
mysql>SELECT*FROM tmp6;+---------------------+| dt                  |+---------------------+|1998-08-0808:08:08||1998-08-0808:08:08||2010-10-1010:10:10||1998-12-0909:09:09||1998-12-0909:09:09||2012-10-1010:10:10||1995-10-0806:06:06||2010-10-1610:50:30|+---------------------+8rowsinset(0.00 sec)

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

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

查看插入结果：

SELECT*FROM tmp6;
mysql>SELECT*FROM tmp6;+---------------------+| dt                  |+---------------------+|1998-08-0808:08:08||1998-08-0808:08:08||2010-10-1010:10:10||1998-12-0909:09:09||1998-12-0909:09:09||2012-10-1010:10:10||1995-10-0806:06:06||2010-10-1610:50:30||2022-08-0515:15:39|+---------------------+9rowsinset(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>CREATETABLE tmp7( ts TIMESTAMP);
Query OK,0rows affected (0.01 sec)
mysql>INSERTINTO tmp7 values('19950101010101'),->('950505050505'),->('1996-02-02 02:02:02'),->('97@03@03 03@03@03'),->(121212121212),->(NOW());
Query OK,6rows affected,1 warning (0.00 sec)
Records: 6  Duplicates: 0Warnings: 1

查看插入结果：

mysql>SELECT*FROM tmp7;+---------------------+| ts                  |+---------------------+|1995-01-0101:01:01||1995-05-0505:05:05||1996-02-0202:02:02||1997-03-0303:03:03||2012-12-1212:12:12||2022-08-0515:18:34|+---------------------+6rowsinset(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≤255VARCHAR(M)变长非二进制字符串L+1字节，在此L≤M和1≤M≤255TINYTEXT非常小的非二进制字符串L+1字节，在此L<2^8TEXT小的非一进制字符串L+2字节，在此L<2^16MEDIUMTEXT中等大小的非二进制字符串L+3字节，在此L<2^24LONGTEXT大的非二进制字符串L+4字节，在此L<2^32ENUM枚举类型，只能有一个枚举字符串值1或2字节，取决于枚举值的数目最大值为65535SET一个设置，字符串对象可以有零个或多个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>CREATETABLE tmp8(-> ch  CHAR(4),-> vch  VARCHAR(4)->);
Query OK,0rows affected (0.01 sec)

输入数据：

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

查询结果：

mysql>SELECT concat('(', ch,')'), concat('(',vch,')')FROM tmp8;+----------------------+---------------------+| concat('(', ch,')')| concat('(',vch,')')|+----------------------+---------------------+|(ab)|(ab  )|+----------------------+---------------------+1rowinset(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>CREATETABLE tmp9( enm ENUM('first','second','third'));
Query OK,0rows affected (0.01 sec)

插入各个列值：

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

查看索引值：

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

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

mysql>CREATETABLE tmp10 (soc INT,levelenum('excellent','good','bad'));
Query OK,0rows affected (0.01 sec)

插入数据：

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

再次插入数据：

mysql>INSERTINTO tmp10 values(100,'best');
ERROR 1265(01000): Data truncated forcolumn'level' at row1

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

mysql>SELECT*FROM tmp10;+------+-----------+| soc  |level|+------+-----------+|70| good      ||90| excellent ||75| good      ||50| bad       |+------+-----------+4rowsinset(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>CREATETABLE tmp11 ( s SET('a','b','c','d'));
Query OK,0rows affected (0.01 sec)

插入数据：

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

再次插入数据：

mysql>INSERTINTO tmp11 values('a,x,b,y');
ERROR 1265(01000): Data truncated forcolumn's' at row1

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

查看结果：

mysql>SELECT*FROM tmp11;+-------+| s     |+-------+| a     || a,b   || a,c,d |+-------+3rowsinset(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)非常小的BLOBL+1字节，在此L<2^8BLOB(M)小BLOBL+2字节，在此L<2^16MEDIUMBLOB(M)中等大小的BLOBL+3字节，在此L<2^24LONGBLOB(M)非常大的BLOBL+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>CREATETABLE tmp12( b BIT(4));
Query OK,0rows affected (0.01 sec)

插入数据：

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

查询插入结果：

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

2、 BINARY和VARBINARY类型

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

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

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

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

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

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

插入数据：

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

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

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

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

3、BLOB类型

BLOB是一个二进制大对象，用来存储可变数量的数据。BLOB类型分为4种: TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB,它们可容纳值的最大长度不同，如下所示：
数据类型存储范围TINYBLOB最大长度为255 (2^8-1) BBLOB最大长度为65535 (2^6-1) BMEDIUMBLOB最大长度为16777215 (2^24-1) BLONGBLOB最大长度为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表结构
字段名数据类型主键外键非空唯一自增myIntINT(10)否否否否否myFloatFLOAT(5,2)否否否否否myTimeTIME否否否否否myCharCHAR(10)否否否否否myBinarybinary(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>CREATEDATABASE myData;
Query OK,1row affected (0.00 sec)

2.2、创建数据表myTable

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

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

向myInt字段插入数据

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

查询myInt字段结果如下

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

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

向myFloat字段插入数据

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

查询myFloat字段结果如下

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

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

向myTime字段插入数据

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

查询myTime字段结果如下

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

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

向myChar字段插入数据

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

查询myChar字段结果如下

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

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

向myBinary字段插入数据

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

查询myBinary字段结果如下

mysql>SELECT myBinary FROM myTable;+--------------------+| myBinary           |+--------------------+|NULL||NULL||NULL||NULL||0x3300000000|+--------------------+5rowsinset(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|+-------+---------+----------+--------+--------------------+5rowsinset(0.00 sec)

2.9、运行过程如下

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

在这里插入图片描述

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

在这里插入图片描述

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

1、新建查询

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

-- 1、创建数据库CREATEDATABASE myData;-- 2、创建数据表myTableUSE myData;CREATETABLE myTable
(
    myInt INT(10),
    myFloat FLOAT(5,2),
    myTime TIME,
    myChar CHAR(10),
    myBinary BINARY(5));-- 3、向数据表myTable插入INT类型数据-- 向myInt字段插入数据INSERTINTO myTable(myInt)VALUES(666);-- 查询myInt字段SELECT myInt FROM myTable;-- 4、向数据表myTable插入FLOAT类型数据-- 向myFloat字段插入数据INSERTINTO myTable(myFloat)VALUES(6.23);-- 查询myFloat字段结SELECT myFloat FROM myTable;-- 5、向数据表myTable插入TIME类型数据-- 向myTime字段插入数据INSERTINTO myTable(myTime)VALUES('10:24:36');-- 查询myTime字段SELECT myTime FROM myTable;-- 6、向数据表myTable插入CHAR类型数据-- 向myChar字段插入数据INSERTINTO myTable(myChar)VALUES('CHAR');-- 查询myChar字段SELECT myChar FROM myTable;-- 7、向数据表myTable插入BINARY类型数据-- 向myBinary字段插入数据INSERTINTO 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数据库的数据类型的方法。

标签：数据库 mysql MySQL数据库

本文转载自: https://blog.csdn.net/m0_47419053/article/details/126181563
版权归原作者 编程爱好者-阿新 所有，如有侵权，请联系我们删除。

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

文章目录