目录1. 数据类型分类2. 数值类型2.1 tinyint类型2.2 bit类型2.3 小数类型2.3.1 float类型2.3.2 decimal类型3. 字符串类型3.1 char类型3.2 varchar类型3.3 char和varchar比较4. 日期和时间类型5. enum和set类型5.1 enum类型5.2 set类型1. 数据类型分类MySQL中的数据类型主要分为数值类型、字符串类型、日期和时间类型、枚举和集合类型等。2. 数值类型2.1 tinyint类型基本语法tinyint[(m)][unsigned]数值越界测试-- 创建表默认有符号mysqlcreatetablett1(numtinyint);Query OK,0rowsaffected(0.02sec)-- 正常插入mysqlinsertintott1values(1);Query OK,1rowaffected(0.00sec)-- 越界插入报错mysqlinsertintott1values(128);ERROR1264(22003):Outofrangevalueforcolumnnumatrow1-- 查询结果mysqlselect*fromtt1;------|num|------|1|------无符号案例-- 创建无符号tinyint表mysqlcreatetablett2(numtinyintunsigned);-- 插入负数报错mysqlinsertintott2values(-1);ERROR1264(22003):Outofrangevalueforcolumnnumatrow1-- 正常插入mysqlinsertintott2values(255);Query OK,1rowaffected(0.02sec)-- 查询结果mysqlselect*fromtt2;------|num|------|255|------重要说明整型默认是有符号的可以通过UNSIGNED关键字指定为无符号建议尽量不使用unsigned对于存放不下的数据直接升级为更大的类型如bigint2.2 bit类型基本语法bit[(M)]M表示每个值的位数范围从1到64默认为1。示例-- 创建表mysqlcreatetablett4(idint,abit(8));Query OK,0rowsaffected(0.01sec)-- 插入数据mysqlinsertintott4values(10,10);Query OK,1rowaffected(0.01sec)-- 查询显示按照ASCII码显示mysqlselect*fromtt4;------------|id|a|------------|10||-------------- 插入ASCII码为65的数据对应字符Amysqlinsertintott4values(65,65);mysqlselect*fromtt4;------------|id|a|------------|10|||65|A|------------bit使用的注意事项bit字段显示时按照ASCII码对应的值显示对于只存放0或1的值可以定义bit(1)来节省空间插入越界值会报错-- 创建存储性别的表mysqlcreatetablett5(genderbit(1));-- 正常插入mysqlinsertintott5values(0);mysqlinsertintott5values(1);-- 越界插入mysqlinsertintott5values(2);ERROR1406(22001):Datatoo longforcolumngenderatrow12.3 小数类型2.3.1 float类型语法float[(m,d)][unsigned]m指定显示长度d指定小数位数占用空间4个字节示例-- 创建表mysqlcreatetablett6(idint,salaryfloat(4,2));Query OK,0rowsaffected(0.01sec)-- 插入数据自动四舍五入mysqlinsertintott6values(100,-99.99);mysqlinsertintott6values(101,-99.991);Query OK,1rowaffected(0.00sec)-- 查询结果mysqlselect*fromtt6;--------------|id|salary|--------------|100|-99.99||101|-99.99|--------------无符号float-- 创建无符号float表mysqlcreatetablett7(idint,salaryfloat(4,2)unsigned);-- 插入负数报错mysqlinsertintott7values(100,-0.1);ERROR1264(22003):Outofrangevalueforcolumnsalaryatrow1-- 正常插入mysqlinsertintott7values(100,0);mysqlinsertintott7values(100,99.99);2.3.2 decimal类型语法decimal(m,d)[unsigned]m指定长度d表示小数点的位数精度比较-- 创建表比较float和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|--------------------------------重要说明float表示的精度大约是7位decimal整数最大位数m为65支持小数最大位数d是30如果希望小数的精度高推荐使用decimal3. 字符串类型3.1 char类型语法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|中国|---------------- 超过最大长度报错mysqlcreatetablett10(idint,namechar(256));ERROR1074(42000):Columnlength too bigforcolumnname(max255)3.2 varchar类型语法varchar(L)可变长度字符串L表示字符长度最大长度65535个字节。示例-- 创建表mysqlcreatetablett10(idint,namevarchar(6));-- 插入数据mysqlinsertintott10values(100,hello);mysqlinsertintott10values(100,我爱你中国);-- 查询结果mysqlselect*fromtt10;--------------------------|id|name|--------------------------|100|hello||100|我爱你中国|--------------------------varchar长度说明实际可用字节数65532因为有1-3个字节用于记录数据大小编码影响utf8编码最大长度 65532/3 21844gbk编码最大长度 65532/2 32766-- 验证utf8编码下的最大长度mysqlcreatetablett11(namevarchar(21845))charsetutf8;ERROR1118(42000):Rowsize too large.mysqlcreatetablett11(namevarchar(21844))charsetutf8;Query OK,0rowsaffected(0.01sec)3.3 char和varchar比较选择建议类型适用场景特点char数据长度固定身份证、手机号、MD5磁盘空间浪费但效率高varchar数据长度变化名字、地址磁盘空间节省但效率低设计原则定长数据使用char直接开辟对应空间变长数据使用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)-- 查询结果timestamp自动填充当前时间mysqlselect*frombirthday;------------------------------------------------------|t1|t2|t3|------------------------------------------------------|1997-07-01|2008-08-0812:01:01|2023-11-1218:28:55|-------------------------------------------------------- 更新数据timestamp自动更新mysqlupdatebirthdaysett12000-1-1;Query OK,1rowaffected(0.00sec)mysqlselect*frombirthday;------------------------------------------------------|t1|t2|t3|------------------------------------------------------|2000-01-01|2008-08-0812:01:01|2023-11-1218:32:09|------------------------------------------------------5. enum和set类型5.1 enum类型语法enum(选项1,选项2,选项3,...)单选类型实际存储数字1,2,3,…最多65535个选项5.2 set类型语法set(选项值1,选项值2,选项值3,...)多选类型实际存储数字1,2,4,8,16,32,…最多64个选项示例-- 创建调查表mysqlcreatetablevotes(-usernamevarchar(30),-hobbyset(登山,游泳,篮球,武术),-genderenum(男,女));Query OK,0rowsaffected(0.02sec)-- 插入数据insertintovotesvalues(雷锋,登山,武术,男);insertintovotesvalues(Juse,登山,武术,2);-- 使用数字标识insertintovotesvalues(LiLei,登山,男);insertintovotesvalues(LiLei,篮球,男);insertintovotesvalues(HanMeiMei,游泳,女);-- 查询所有数据mysqlselect*fromvotes;----------------------------------|username|hobby|gender|----------------------------------|雷锋|登山,武术|男||Juse|登山,武术|女||LiLei|登山|男||LiLei|篮球|男||HanMeiMei|游泳|女|----------------------------------集合查询-- 错误查询方式只能查询完全匹配mysqlselect*fromvoteswherehobby登山;--------------------------|username|hobby|gender|--------------------------|LiLei|登山|男|---------------------------- 正确查询方式使用find_in_set函数mysqlselect*fromvoteswherefind_in_set(登山,hobby);---------------------------------|username|hobby|gender|---------------------------------|雷锋|登山,武术|男||Juse|登山,武术|女||LiLei|登山|男|---------------------------------find_in_set函数-- 语法find_in_set(sub, 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|---------------------------
