数据类型分类

MySQL数据库支持多种数据类型，可以用于定义列的数据类型，以下是 MySQL 中常见的数据类型：

不同的数据类型在存储和处理数据时，会有不同的性能和空间占用，因此在设计数据表时，需要根据实际需求来选择合适的数据类型来存储数据。

数值类型

在使用数值类型时，应该根据实际需求选择合适的数据类型和长度，以避免数据溢出或浪费存储空间的问题。同时，应该尽量避免使用浮点数类型进行计算，因为浮点数的精度有限，可能会导致精度误差。

tinyint类型

在 MySQL 中，TINYINT 是一种整数类型，它使用 1 个字节来存储整数数据。TINYINT 乐意存储的范围为 -128 ~ 127（有符号），0 ~ 255（无符号）。

TINYINT 类型最常用的情况是，用于标记某个状态或者开关，例如：开关状态、是否删除等。由于 TINYINT 只使用一个字节，因此在存储空间上比其它整数类型更加节省。

有符号 TINYINT 类型范围测试：

无符号 TINYINT 类型范围测试：

• 在 MySQL 中整形可以指定是有符号的和无符号的，默认情况下是有符号的。
• 可以通过 unsigned 来定义无符号 TINYINT。

注意：尽量不要使用 unsigned ，对于 int 类型可能存放不下的数据，int unsigned同样可能存放不下，与其如此，不如在设计时，将 int 类型提升为 bigint 类型。

bit类型

在 MySQL 中，bit 数据类型用于存储位数据。它可以存储 1 个比特位或多个比特位，最多可以存储 64 个比特位。

基本语法：

bit[(M)：位字段类型，M表示每个值的位数，范围从1到64。如果M被忽略，默认为1.

在创建表时，使用 bit 数据类型定义一个变量，如下所示：

在表中插入数据如下，插入数据后发现一个异常现象，bit类型的数据 10 没有能够正确显示：

在 MySQL 中，bit 类型的数据在显示时按照 ASCII 码表对于的值进行显示。在 ASCII 码表中，10 表示的是控制字符 LF，即换行符。因此查询显示时就会发生异常。若向表中插入记录 id 和 flag 均为 65，则在 ASCII 中对应的是 A，如下所示：

bit 使用的注意事项：

• bit 字段在显示时，是按照 ASCII 码表对应的值显示的。
• 若某个变量只有两种状态（如0或1），这时可以定义 bit(1)，可更好的节省空间。

float类型

在 MySQL 中，float 是一种浮点数数据类型，用于存储具有小数部分的数字。float 类型需要指定精度和范围。

语法：

float[(m,d)] [unsigned] : M指定显示长度，d指定小数位数，占用空间4个字节。

示例1：float(4,2) 表示的范围是 -99.99 ~ 99.99，MySQL 在保存值时会进行四舍五入。

创建一个表，表中包含一个 float(4,2) 类型的列，默认情况下是有符号类型，如下所示：

float(4,2) 的范围是 -99.99 ~ 99.99，但是 MySQL 在保存数据时会进行四舍五入，因此 float(4,2) 的实际范围是-99.994 ~ 99.994。因此插入该范围的数据都能成功：

在 t1 表中插入该范围以外的数据，插入就会出错：

示例2：若定义的是 float(5,2) unsigned 时，因为是无符号的，所以范围是 0 ~ 999.99。

创建一个表，表中包含一个 float(5,2) 类型的列，将该列定义为无符号，如下所示：

MySQL 在保存数据时会进行四舍五入，因此 float(5,2) 的实际范围是0 ~ 999.994。因此插入该范围的数据都能成功：

在 t2 表中插入该范围以外的数据，插入就会出错：

decimal类型

在 MySQL 中，decimal 是一种精确数值类型，用于存储具有小数部分的数字。decimal 类型需要指定精度和范围，它可以存储高精度的十进制数。可以存储从 -10^38+1 到 10^38-1的数字，精度可以达到65位小数。DECIMAL类型在存储和计算数字时保持精度不变，因此适用于需要高精度计算的场合，如财务，金融等。

语法：

decimal(m,d) [unsigned] : 定点数m指定长度，d表示小数点的位数。

说明：

• decimal(5,2) 表示的范围是 -999.99 ~ 999.99。
• decimal(5,2) unsigned 表示的范围是 0 ~ 999.99。decimal 和 float 很像，但是有区别。float 和 decimal 表示的精度不一样。

示例：创建精度为 10，小数位为 8 的 decimal 类型的列，和创建精度为 10，小数位为 8 的 float 类型的列，如下所示：

向表中插入数据，观察 float 和 decimal 的区别：

总结：综上所述，若需要进行高效的计算和存储，可选择 float 类型，但是需要注意精度损失的问题；若对数据精度要求比较高，可选择 decimal 类型，但需要注意存储空间的使用。在实际情况下，需要具体根据业务的需求和数据特点来选择合适的数据类型。

在 MySQL 中，float 数据类型的精度大约为 7 位，而 decimal 数据类型支持高精度的计算，整数最大位数为 65 位，小数最大位数为 30 位。若未指定小数位数，则默认为0，若未指定整数位数，则默认为10。

字符串类型

char类型

在 MySQL 中，char 类型是一种固定长度的字符类型。它用于存储长度固定且较短的字符串。它可以存储 0~255 个字符，但是实际存储的字符数值取决于定义时指定的长度。

语法：

char(L) : 固定长度字符串，L是可以存储的长度，单位为字符，最大长度值可以为255.

示例：创建一个长度为10的 char 类型的语句如下：

向表中插入数据测试，如下所示，它的存储长度不超过10个字符的字符串：

需要注意的是，char 类型的存储空间是固定的，无论实际存储的字符串长度是多少，都会占用指定长度的存储空间。因此，char 类型的存储空间通常比较浪费，适合存储长度固定的字符串。如果需要存储长度不固定的字符串，可以考虑使用 varchar 类型。但是 char 类型相较于 varchar 类型更有效率。

varchar类型

在 MySQL 中，varchar 类型是一种可变长度的字符类型。它用于存储长度可变的字符串，且不会浪费存储空间。varchar 类型可以存储 0 ~ 65535 个字符。

语法：

varchar(L): 可变长度字符串，L表示字符长度，最大长度为65535个字节。

示例：定义一个最大长度为10的 varchar 类型的语句如下：

向表中插入数据进行测试：

关于 varchar(len)，len 到底多大，这个 len 值，和表的编码密切相关：

• varchar 长度可以指定为 0 ~ 65535 直接的值，但是有 1-3 个字节用于记录数据大小，所以说有效字节数是 65532。
• 若编码是 utf8 时，varchar(n) 的参数 n 最大值是 65532 / 3 = 21844（因为 utf 中，一个字符占用 3 个字节），若编码是 gbk，varchar(n) 的参数 n 最大值是 65532 / 2 = 32766（gbk 中，一个字符占用两个字节）。

使用 utf8 进行验证：

需要注意的是，varchar 类型的索引效率不如 char 类型高。若 varchar 的长度定义过大，可能会导致额外的存储空间占用和查询效率降低的问题。因此定义 varchar 类型时，应根据实际情况选择适合长度。

char vs varchar

char 类型和 varchar 类型比较：

• 存储方式：char 类型是一种固定长度的字符类型，无论实际存储的字符数是多少，都会占用固定长度的存储空间。varchar 类型是一种可变长的字符类型，实际存储空间取决于存储的字符串长度。
• 存储空间：char 类型相对于 varchar 类型更浪费存储空间。
• 索引效率：char 类型的列上建立索引效率更高，在 varchar 类型上建立索引效率相对较低。
• 使用场景：char 类型适合存储长度固定的字符串，例如：身份证号码、银行卡号等。varchar类型适合存储长度不固定的字符串，例如：用户名，地址等。

如何选择 char 类型或者 varchar 类型：

• 如果数据长度固定，使用 char ，如：身份证、手机号等。
• 如果数据长度不固定，使用 varchar，如：姓名、地址等，但是要保证最长的能够存进去。
• char 类型浪费空间，效率高；varchar 类型节省空间，效率低。
• 定长的意义是直接开辟好相应空间，变长的意义是在不超过自定义范围的情况下，使用多少，开辟多少。

日期和时间类型

常用的日期有如下三个：

• date :日期 yyyy-mm-dd ，占用三字节。
• datetime :时间日期格式 yyyy-mm-dd HH:ii:ss 表示范围从 1000 到 9999 ，占用八字节。
• timestamp ：时间戳，从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致，占用四字节。

示例：创建一个表，其中包含以上3个时间日期类型，如下所示：

向表中插入数据，若不手动输入第三列，则默认获取当前时间：

注意：MySQL 中时间和日期类型的取值范围是有限制的，因此在存储时间和日期数据时需要注意数据的合法性。timestamp 类型比较特殊，它会在数据行发生修改时自动更新当前时间。这个行为可以通过 ON UPDATE CURRENT_TIMESTAMP 来控制。

enum和set类型

在 MySQL 中，enum 和 set 是两种常见的枚举类型。

enum 类型： 表示枚举值，只能取预定义的值之一。enum 类型可以包含多个枚举值，每个枚举值用逗号分隔。

该设定只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值；而出于效率考虑，这些值实际存储的是 “数字”，因为这些选项的每个选项值依次对应如下数字：1，2，3，4 … 最多可以有 65535 个；当我们添加枚举值时，也可以添加对应的数字编号。

set 类型： 也可以表示枚举值，但它可以取多个预定义的值。set 类型可以包含多个枚举值，每个枚举值使用逗号分隔。

该设定只是提供了若干个选项的值，最终一个单元格中，设计可存储了其中任意多个值；而且出于效率考虑，这些值
实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,4,8,16,32，… 最多64个。

示例：有一个调查表 votes，需要调查人们喜欢的颜色，如在（红色、绿色、黄色、黑色等）中去选择【多选】，（男，女）【单选】：

向表中插入数据如下：

如上，我们可以使用数字来标识各个值。在 set 中，可以使用二进制来表示选中哪些选项（例如，上面 set 中插入10 ，10 的二进制是 1010，从表中列举的值从右向左看，1表示选择，0表示未选中）。enum 中单项选择直接使用数字进行选择。

需要注意的是，虽然 enum 和 set 可以用数字表示选项，但这通常不是最佳实践。如果你使用数字而不是文本值，可能会使数据更难以理解和维护。

有以下数据，查找所有喜欢绿色的人：

当我们使用 where 进行条件筛选时，选出的是只喜欢绿色的人，而没有将喜欢的颜色有绿色的人全部查找出来，这样显然是不行的。接下来我们介绍一个函数，用于完成该查询。

集合查询使用 find_in_set 函数：

在 MySQL 中，find_in_set() 函数用于在一个逗号分隔的字符串中查找一个子字符串，并返回其在字符串中的位置。该函数语法如下：

find_in_set(sub,str_list);

如果sub在str_list中，则返回下标；如果不在，则返回0；str_list使用逗号分开。

示例：函数返回的是第一次出现 ‘a’ 的位置的下标。

查询表 votes 中喜欢的颜色有绿色的：