1. 前言写 MySQL 的时候建表是最基础也最容易翻车的一步。表建得好不好很大程度上取决于你对数据类型的理解。类型选错了轻则浪费空间重则数据存不进去、查询出问题。这篇博客把 MySQL 里常用的数据类型过一遍包括数值、字符串、日期、枚举和集合每个都配上实际执行的 SQL 和结果方便你照着敲一遍。2. 数值类型数值类型是建表时用得最多的先看分类再逐个说细节。2.1 tinyint 类型tinyint 默认是有符号的范围是 -128 到 127。如果你往里面塞一个超出范围的值MySQL 会直接报错。mysqlcreatetablett1(numtinyint);Query OK,0rowsaffected(0.02sec)mysqlinsertintott1values(1);Query OK,1rowaffected(0.00sec)mysqlinsertintott1values(128);-- 越界插入报错ERROR1264(22003):Outofrangevalueforcolumnnumatrow1mysqlselect*fromtt1;------|num|------|1|------1rowinset(0.00sec)MySQL 的整型默认是有符号的如果你想让某个字段只能存非负数可以加上unsigned关键字。mysqlcreatetablett2(numtinyintunsigned);mysqlinsertintott2values(-1);-- 无符号范围是 0 - 255ERROR1264(22003):Outofrangevalueforcolumnnumatrow1mysqlinsertintott2values(255);Query OK,1rowaffected(0.02sec)mysqlselect*fromtt2;------|num|------|255|------1rowinset(0.00sec)这里有个建议尽量别用 unsigned。比如 int 类型存不下的数据int unsigned 同样可能存不下与其这样不如在建表时直接把 int 提升成 bigint省得后面出问题。2.2 bit 类型bit 是位字段类型语法是bit[(M)]M 表示每个值的位数范围 1 到 64不写的话默认是 1。mysqlcreatetablett4(idint,abit(8));Query OK,0rowsaffected(0.01sec)mysqlinsertintott4values(10,10);Query OK,1rowaffected(0.01sec)mysqlselect*fromtt4;-- 发现很怪异的现象a的数据10没有出现------------|id|a|------------|10||------------1rowinset(0.00sec)这里有个坑bit 字段在显示的时候是按照 ASCII 码对应的值显示的。所以存 10 显示不出来存 65 会显示成A。mysqlinsertintott4values(65,65);mysqlselect*fromtt4;------------|id|a|------------|10|||65|A|------------如果你只需要存 0 或 1可以定义成bit(1)这样能省空间。mysqlcreatetablett5(genderbit(1));mysqlinsertintott5values(0);Query OK,1rowaffected(0.00sec)mysqlinsertintott5values(1);Query OK,1rowaffected(0.00sec)mysqlinsertintott5values(2);-- 当插入2时已经越界了ERROR1406(22001):Datatoo longforcolumngenderatrow12.3 小数类型小数类型主要有 float 和 decimal 两种各有各的适用场景。2.3.1 floatfloat 的语法是float[(m, d)] [unsigned]M 指定显示长度d 指定小数位数占用 4 个字节。mysqlcreatetablett6(idint,salaryfloat(4,2));Query OK,0rowsaffected(0.01sec)mysqlinsertintott6values(100,-99.99);Query OK,1rowaffected(0.00sec)mysqlinsertintott6values(101,-99.991);-- 多的这一点被拿掉了Query OK,1rowaffected(0.00sec)mysqlselect*fromtt6;--------------|id|salary|--------------|100|-99.99||101|-99.99|--------------2rowsinset(0.00sec)float(4,2)表示的范围是 -99.99 到 99.99MySQL 在保存值的时候会做四舍五入。如果指定了 unsigned范围就变成 0 到 99.99。mysqlcreatetablett7(idint,salaryfloat(4,2)unsigned);mysqlinsertintott7values(100,-0.1);Query OK,1rowaffected,1warning(0.00sec)mysqlshowwarnings;----------------------------------------------------------------|Level|Code|Message|----------------------------------------------------------------|Warning|1264|Outofrangevalueforcolumnsalaryatrow1|----------------------------------------------------------------1rowinset(0.00sec)mysqlinsertintott7values(100,-0);Query OK,1rowaffected(0.00sec)mysqlinsertintott7values(100,99.99);Query OK,1rowaffected(0.00sec)2.3.2 decimaldecimal 是定点数语法是decimal(m, d)m 指定长度d 表示小数位数。decimal(5,2)表示范围是 -999.99 到 999.99unsigned 的话是 0 到 999.99。float 和 decimal 最大的区别是精度。float 的精度大约是 7 位decimal 的整数最大位数 m 是 65小数最大位数 d 是 30。如果希望精度高推荐用 decimal。mysqlcreatetablett8(idint,salaryfloat(10,8),salary2decimal(10,8));mysqlinsertintott8values(100,23.12345612,23.12345612);Query OK,1rowaffected(0.00sec)mysqlselect*fromtt8;--------------------------------|id|salary|salary2|--------------------------------|100|23.12345695|23.12345612|-- 发现decimal的精度更准确--------------------------------同样的值float 存出来变成了 23.12345695decimal 还是 23.12345612。所以对精度有要求的场景比如金额直接用 decimal。3. 字符串类型字符串类型主要就是 char 和 varchar这俩是面试常客也是建表时最容易纠结的。3.1 charchar 是固定长度字符串语法是char(L)L 是存储的长度单位是字符最大 255。mysqlcreatetablett9(idint,namechar(2));Query OK,0rowsaffected(0.00sec)mysqlinsertintott9values(100,ab);Query OK,1rowaffected(0.00sec)mysqlinsertintott9values(101,中国);Query OK,1rowaffected(0.00sec)mysqlselect*fromtt9;--------------|id|name|--------------|100|ab||101|中国|--------------char(2) 可以放两个字符字母或汉字都行但超过 255 就会报错。mysqlcreatetablett10(idint,namechar(256));ERROR1074(42000):Columnlength too bigforcolumnname(max255);useBLOBorTEXTinstead3.2 varcharvarchar 是可变长度字符串语法是varchar(L)L 表示字符长度最大 65535 个字节。mysqlcreatetablett10(idint,namevarchar(6));-- 表示这里可以存放6个字符mysqlinsertintott10values(100,hello);mysqlinsertintott10values(100,我爱你中国);mysqlselect*fromtt10;--------------------------|id|name|--------------------------|100|hello||100|我爱你中国|--------------------------关于 varchar(len) 到底能设多大这个和表的编码密切相关。varchar 长度可以指定 0 到 65535 之间的值但有 1 到 3 个字节用于记录数据大小所以有效字节数是 65532。表编码是 utf8 时一个字符占 3 个字节varchar(n) 的 n 最大值是 65532/3 21844表编码是 gbk 时一个字符占 2 个字节varchar(n) 的 n 最大值是 65532/2 32766验证一下mysqlcreatetablett11(namevarchar(21845))charsetutf8;-- 验证了utf8确实是不能超过21844ERROR1118(42000):Rowsize too large.The maximumrowsizeforthe usedtabletype,notcounting BLOBs,is65535.You havetochangesomecolumnstoTEXTorBLOBs mysqlcreatetablett11(namevarchar(21844))charsetutf8;Query OK,0rowsaffected(0.01sec)3.3 char 和 varchar 怎么选这个选择其实不难记住几条原则就行如果数据长度确定都一样用定长 char比如身份证、手机号、md5如果数据长度有变化用变长 varchar比如名字、地址但要保证最长的能存进去定长的磁盘空间比较浪费但效率高变长的磁盘空间比较节省但效率低定长的意义是直接开辟好对应的空间变长的意义是在不超过自定义范围的情况下用多少开辟多少。4. 日期和时间类型常用的日期类型有三个date日期格式yyyy-mm-dd占用 3 字节datetime时间日期格式yyyy-mm-dd HH:ii:ss范围从 1000 到 9999占用 8 字节timestamp时间戳从 1970 年开始格式和 datetime 完全一致占用 4 字节-- 创建表mysqlcreatetablebirthday(t1date,t2datetime,t3timestamp);Query OK,0rowsaffected(0.01sec)-- 插入数据mysqlinsertintobirthday(t1,t2)values(1997-7-1,2008-8-8 12:1:1);Query OK,1rowaffected(0.00sec)mysqlselect*frombirthday;------------------------------------------------------|t1|t2|t3|------------------------------------------------------|1997-07-01|2008-08-0812:01:01|2017-11-1218:28:55|-- 添加数据时时间戳自动补上当前时间-------------------------------------------------------- 更新数据mysqlupdatebirthdaysett12000-1-1;Query OK,1rowaffected(0.00sec)mysqlselect*frombirthday;------------------------------------------------------|t1|t2|t3|------------------------------------------------------|2000-01-01|2008-08-0812:01:01|2017-11-1218:32:09|-- 更新数据时间戳会更新成当前时间------------------------------------------------------注意 timestamp 的特性插入数据时自动补上当前时间更新数据时也会自动更新成当前时间。这个特性在很多场景下很实用。5. enum 和 set这俩是 MySQL 里比较特殊的类型一个是单选一个是多选。5.1 enum 枚举enum 是单选类型语法是enum(选项1,选项2,选项3,...)。它只提供若干个选项最终一个单元格只存其中一个值。出于效率考虑这些值实际存储的是数字每个选项依次对应 1,2,3…最多 65535 个。添加枚举值时也可以直接加数字编号。5.2 set 集合set 是多选类型语法是set(选项值1,选项值2,选项值3,...)。一个单元格可以存其中任意多个值。同样出于效率考虑这些值实际存储的是数字每个选项依次对应 1,2,4,8,16,32…最多 64 个。这里有个建议添加枚举值、集合值的时候别用数字方式不利于阅读。5.3 实际案例建一个调查表 votes调查人的喜好比如登山、游泳、篮球、武术可多选性别单选。mysqlcreatetablevotes(-usernamevarchar(30),-hobbyset(登山,游泳,篮球,武术),-- 使用数字标识每个爱好想想Linux权限采用比特位位置-genderenum(男,女));-- 使用数字标识的时候就是正常的数组下标Query OK,0rowsaffected(0.02sec)insertintovotesvalues(雷锋,登山,武术,男);insertintovotesvalues(Juse,登山,武术,2);select*fromvoteswheregender2;---------------------------------|username|hobby|gender|---------------------------------|Juse|登山,武术|女|---------------------------------现在想查找所有喜欢登山的人如果直接用等值查询mysqlselect*fromvoteswherehobby登山;--------------------------|username|hobby|gender|--------------------------|LiLei|登山|男|--------------------------这样查不出来所有爱好为登山的人因为有的人爱好是登山,武术。这时候要用find_in_set函数。find_in_set(sub, str_list)如果 sub 在 str_list 中返回下标如果不在返回 0。str_list 是用逗号分隔的字符串。mysqlselectfind_in_set(a,a,b,c);---------------------------|find_in_set(a,a,b,c)|---------------------------|1|---------------------------mysqlselectfind_in_set(d,a,b,c);---------------------------|find_in_set(d,a,b,c)|---------------------------|0|---------------------------mysqlselect*fromvoteswherefind_in_set(登山,hobby);---------------------------------|username|hobby|gender|---------------------------------|雷锋|登山,武术|男||Juse|登山,武术|女||LiLei|登山|男|---------------------------------这样就能把所有喜欢登山的人都查出来了。6. 小结数据类型这块记住几个关键点就够了整型默认有符号unsigned 能不用就不用存不下就升级 bigintbit 类型显示按 ASCII 码只存 0/1 用 bit(1)小数精度要求高用 decimal别用 float定长用 char变长用 varchar注意编码对 varchar 长度的影响timestamp 会自动更新适合记录操作时间enum 单选、set 多选查询 set 用 find_in_set建表前先想清楚每个字段的类型后面能省不少事。 
长河君 · 2026/10/6 2:47:47 · 阅读 1.2k MySQL 数据类型详解：从数值到字符串，一篇讲透
1. 前言写 MySQL 的时候建表是最基础也最容易翻车的一步。表建得好不好很大程度上取决于你对数据类型的理解。类型选错了轻则浪费空间重则数据存不进去、查询出问题。这篇博客把 MySQL 里常用的数据类型过一遍包括数值、字符串、日期、枚举和集合每个都配上实际执行的 SQL 和结果方便你照着敲一遍。2. 数值类型数值类型是建表时用得最多的先看分类再逐个说细节。2.1 tinyint 类型tinyint 默认是有符号的范围是 -128 到 127。如果你往里面塞一个超出范围的值MySQL 会直接报错。mysqlcreatetablett1(numtinyint);Query OK,0rowsaffected(0.02sec)mysqlinsertintott1values(1);Query OK,1rowaffected(0.00sec)mysqlinsertintott1values(128);-- 越界插入报错ERROR1264(22003):Outofrangevalueforcolumnnumatrow1mysqlselect*fromtt1;------|num|------|1|------1rowinset(0.00sec)MySQL 的整型默认是有符号的如果你想让某个字段只能存非负数可以加上unsigned关键字。mysqlcreatetablett2(numtinyintunsigned);mysqlinsertintott2values(-1);-- 无符号范围是 0 - 255ERROR1264(22003):Outofrangevalueforcolumnnumatrow1mysqlinsertintott2values(255);Query OK,1rowaffected(0.02sec)mysqlselect*fromtt2;------|num|------|255|------1rowinset(0.00sec)这里有个建议尽量别用 unsigned。比如 int 类型存不下的数据int unsigned 同样可能存不下与其这样不如在建表时直接把 int 提升成 bigint省得后面出问题。2.2 bit 类型bit 是位字段类型语法是bit[(M)]M 表示每个值的位数范围 1 到 64不写的话默认是 1。mysqlcreatetablett4(idint,abit(8));Query OK,0rowsaffected(0.01sec)mysqlinsertintott4values(10,10);Query OK,1rowaffected(0.01sec)mysqlselect*fromtt4;-- 发现很怪异的现象a的数据10没有出现------------|id|a|------------|10||------------1rowinset(0.00sec)这里有个坑bit 字段在显示的时候是按照 ASCII 码对应的值显示的。所以存 10 显示不出来存 65 会显示成A。mysqlinsertintott4values(65,65);mysqlselect*fromtt4;------------|id|a|------------|10|||65|A|------------如果你只需要存 0 或 1可以定义成bit(1)这样能省空间。mysqlcreatetablett5(genderbit(1));mysqlinsertintott5values(0);Query OK,1rowaffected(0.00sec)mysqlinsertintott5values(1);Query OK,1rowaffected(0.00sec)mysqlinsertintott5values(2);-- 当插入2时已经越界了ERROR1406(22001):Datatoo longforcolumngenderatrow12.3 小数类型小数类型主要有 float 和 decimal 两种各有各的适用场景。2.3.1 floatfloat 的语法是float[(m, d)] [unsigned]M 指定显示长度d 指定小数位数占用 4 个字节。mysqlcreatetablett6(idint,salaryfloat(4,2));Query OK,0rowsaffected(0.01sec)mysqlinsertintott6values(100,-99.99);Query OK,1rowaffected(0.00sec)mysqlinsertintott6values(101,-99.991);-- 多的这一点被拿掉了Query OK,1rowaffected(0.00sec)mysqlselect*fromtt6;--------------|id|salary|--------------|100|-99.99||101|-99.99|--------------2rowsinset(0.00sec)float(4,2)表示的范围是 -99.99 到 99.99MySQL 在保存值的时候会做四舍五入。如果指定了 unsigned范围就变成 0 到 99.99。mysqlcreatetablett7(idint,salaryfloat(4,2)unsigned);mysqlinsertintott7values(100,-0.1);Query OK,1rowaffected,1warning(0.00sec)mysqlshowwarnings;----------------------------------------------------------------|Level|Code|Message|----------------------------------------------------------------|Warning|1264|Outofrangevalueforcolumnsalaryatrow1|----------------------------------------------------------------1rowinset(0.00sec)mysqlinsertintott7values(100,-0);Query OK,1rowaffected(0.00sec)mysqlinsertintott7values(100,99.99);Query OK,1rowaffected(0.00sec)2.3.2 decimaldecimal 是定点数语法是decimal(m, d)m 指定长度d 表示小数位数。decimal(5,2)表示范围是 -999.99 到 999.99unsigned 的话是 0 到 999.99。float 和 decimal 最大的区别是精度。float 的精度大约是 7 位decimal 的整数最大位数 m 是 65小数最大位数 d 是 30。如果希望精度高推荐用 decimal。mysqlcreatetablett8(idint,salaryfloat(10,8),salary2decimal(10,8));mysqlinsertintott8values(100,23.12345612,23.12345612);Query OK,1rowaffected(0.00sec)mysqlselect*fromtt8;--------------------------------|id|salary|salary2|--------------------------------|100|23.12345695|23.12345612|-- 发现decimal的精度更准确--------------------------------同样的值float 存出来变成了 23.12345695decimal 还是 23.12345612。所以对精度有要求的场景比如金额直接用 decimal。3. 字符串类型字符串类型主要就是 char 和 varchar这俩是面试常客也是建表时最容易纠结的。3.1 charchar 是固定长度字符串语法是char(L)L 是存储的长度单位是字符最大 255。mysqlcreatetablett9(idint,namechar(2));Query OK,0rowsaffected(0.00sec)mysqlinsertintott9values(100,ab);Query OK,1rowaffected(0.00sec)mysqlinsertintott9values(101,中国);Query OK,1rowaffected(0.00sec)mysqlselect*fromtt9;--------------|id|name|--------------|100|ab||101|中国|--------------char(2) 可以放两个字符字母或汉字都行但超过 255 就会报错。mysqlcreatetablett10(idint,namechar(256));ERROR1074(42000):Columnlength too bigforcolumnname(max255);useBLOBorTEXTinstead3.2 varcharvarchar 是可变长度字符串语法是varchar(L)L 表示字符长度最大 65535 个字节。mysqlcreatetablett10(idint,namevarchar(6));-- 表示这里可以存放6个字符mysqlinsertintott10values(100,hello);mysqlinsertintott10values(100,我爱你中国);mysqlselect*fromtt10;--------------------------|id|name|--------------------------|100|hello||100|我爱你中国|--------------------------关于 varchar(len) 到底能设多大这个和表的编码密切相关。varchar 长度可以指定 0 到 65535 之间的值但有 1 到 3 个字节用于记录数据大小所以有效字节数是 65532。表编码是 utf8 时一个字符占 3 个字节varchar(n) 的 n 最大值是 65532/3 21844表编码是 gbk 时一个字符占 2 个字节varchar(n) 的 n 最大值是 65532/2 32766验证一下mysqlcreatetablett11(namevarchar(21845))charsetutf8;-- 验证了utf8确实是不能超过21844ERROR1118(42000):Rowsize too large.The maximumrowsizeforthe usedtabletype,notcounting BLOBs,is65535.You havetochangesomecolumnstoTEXTorBLOBs mysqlcreatetablett11(namevarchar(21844))charsetutf8;Query OK,0rowsaffected(0.01sec)3.3 char 和 varchar 怎么选这个选择其实不难记住几条原则就行如果数据长度确定都一样用定长 char比如身份证、手机号、md5如果数据长度有变化用变长 varchar比如名字、地址但要保证最长的能存进去定长的磁盘空间比较浪费但效率高变长的磁盘空间比较节省但效率低定长的意义是直接开辟好对应的空间变长的意义是在不超过自定义范围的情况下用多少开辟多少。4. 日期和时间类型常用的日期类型有三个date日期格式yyyy-mm-dd占用 3 字节datetime时间日期格式yyyy-mm-dd HH:ii:ss范围从 1000 到 9999占用 8 字节timestamp时间戳从 1970 年开始格式和 datetime 完全一致占用 4 字节-- 创建表mysqlcreatetablebirthday(t1date,t2datetime,t3timestamp);Query OK,0rowsaffected(0.01sec)-- 插入数据mysqlinsertintobirthday(t1,t2)values(1997-7-1,2008-8-8 12:1:1);Query OK,1rowaffected(0.00sec)mysqlselect*frombirthday;------------------------------------------------------|t1|t2|t3|------------------------------------------------------|1997-07-01|2008-08-0812:01:01|2017-11-1218:28:55|-- 添加数据时时间戳自动补上当前时间-------------------------------------------------------- 更新数据mysqlupdatebirthdaysett12000-1-1;Query OK,1rowaffected(0.00sec)mysqlselect*frombirthday;------------------------------------------------------|t1|t2|t3|------------------------------------------------------|2000-01-01|2008-08-0812:01:01|2017-11-1218:32:09|-- 更新数据时间戳会更新成当前时间------------------------------------------------------注意 timestamp 的特性插入数据时自动补上当前时间更新数据时也会自动更新成当前时间。这个特性在很多场景下很实用。5. enum 和 set这俩是 MySQL 里比较特殊的类型一个是单选一个是多选。5.1 enum 枚举enum 是单选类型语法是enum(选项1,选项2,选项3,...)。它只提供若干个选项最终一个单元格只存其中一个值。出于效率考虑这些值实际存储的是数字每个选项依次对应 1,2,3…最多 65535 个。添加枚举值时也可以直接加数字编号。5.2 set 集合set 是多选类型语法是set(选项值1,选项值2,选项值3,...)。一个单元格可以存其中任意多个值。同样出于效率考虑这些值实际存储的是数字每个选项依次对应 1,2,4,8,16,32…最多 64 个。这里有个建议添加枚举值、集合值的时候别用数字方式不利于阅读。5.3 实际案例建一个调查表 votes调查人的喜好比如登山、游泳、篮球、武术可多选性别单选。mysqlcreatetablevotes(-usernamevarchar(30),-hobbyset(登山,游泳,篮球,武术),-- 使用数字标识每个爱好想想Linux权限采用比特位位置-genderenum(男,女));-- 使用数字标识的时候就是正常的数组下标Query OK,0rowsaffected(0.02sec)insertintovotesvalues(雷锋,登山,武术,男);insertintovotesvalues(Juse,登山,武术,2);select*fromvoteswheregender2;---------------------------------|username|hobby|gender|---------------------------------|Juse|登山,武术|女|---------------------------------现在想查找所有喜欢登山的人如果直接用等值查询mysqlselect*fromvoteswherehobby登山;--------------------------|username|hobby|gender|--------------------------|LiLei|登山|男|--------------------------这样查不出来所有爱好为登山的人因为有的人爱好是登山,武术。这时候要用find_in_set函数。find_in_set(sub, str_list)如果 sub 在 str_list 中返回下标如果不在返回 0。str_list 是用逗号分隔的字符串。mysqlselectfind_in_set(a,a,b,c);---------------------------|find_in_set(a,a,b,c)|---------------------------|1|---------------------------mysqlselectfind_in_set(d,a,b,c);---------------------------|find_in_set(d,a,b,c)|---------------------------|0|---------------------------mysqlselect*fromvoteswherefind_in_set(登山,hobby);---------------------------------|username|hobby|gender|---------------------------------|雷锋|登山,武术|男||Juse|登山,武术|女||LiLei|登山|男|---------------------------------这样就能把所有喜欢登山的人都查出来了。6. 小结数据类型这块记住几个关键点就够了整型默认有符号unsigned 能不用就不用存不下就升级 bigintbit 类型显示按 ASCII 码只存 0/1 用 bit(1)小数精度要求高用 decimal别用 float定长用 char变长用 varchar注意编码对 varchar 长度的影响timestamp 会自动更新适合记录操作时间enum 单选、set 多选查询 set 用 find_in_set建表前先想清楚每个字段的类型后面能省不少事。