概述常见数据类型的属性整数类型CREATE TABLE test_int1( f1 TINYINT, f2 SMALLINT, f3 MEDIUMINT, f4 INTEGER, f5 BIGINT );可选属性①显示宽度可以为每一个字段设置宽度[位数]但不具备强制力[取决于字段类型本身]CREATE TABLE test_int2( f1 INT, f2 INT(5), f3 INT(5) ZEROFILL #① 显示宽度为5。当insert的值不足5位时使用0填充。 #②当使用ZEROFILL时自动会添加UNSIGNED ) INSERT INTO test_int2(f1,f2) VALUES(123,123),(123456,123456); SELECT * FROM test_int2; INSERT INTO test_int2(f3) VALUES(123),(123456);②UNSIGNED无符号类型非负所有的整数类型都有一个可选的属性UNSIGNED无符号属性无符号整数类型的最小取值为0[增加一个宽度]CREATE TABLE test_int3( f1 INT UNSIGNED ); DESC test_int3; INSERT INTO test_int3 VALUES(2412321); #Out of range value for column f1 at row 1 INSERT INTO test_int3 VALUES(4294967296);③ZEROFILL : 0填充结合宽度可以调整展示样式适用场景TINYINT 一般用于枚举数据比如系统设定取值范围很小且固定的场景。SMALLINT 可以用于较小范围的统计数据比如统计工厂的固定资产库存数量等。MEDIUMINT 用于较大整数的计算比如车站每日的客流量等。INT、INTEGER 取值范围足够大一般情况下不用考虑超限问题用得最多。比如商品编号。BIGINT 只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证 券公司衍生产品持仓等。要考虑存储空间和可靠性的平衡问题浮点类型MySQL 存储浮点数的格式为符号(S) 、 尾数(M) 和 阶码(E)。MySQL支持非标准语法FLOAT(M,D) 或 DOUBLE(M,D)。这里M称为 精度 D称为 标度。(M,D)中M整数位小数 位D小数位。 DM2550D30。CREATE TABLE test_double1( f1 FLOAT, f2 FLOAT(5,2), f3 DOUBLE, f4 DOUBLE(5,2) ); DESC test_double1; INSERT INTO test_double1(f1,f2) VALUES(123.45,123.45); SELECT * FROM test_double1; INSERT INTO test_double1(f3,f4) VALUES(123.45,123.456); #存在四舍五入 #Out of range value for column f4 at row 1 INSERT INTO test_double1(f3,f4) VALUES(123.45,1234.456); #Out of range value for column f4 at row 1 INSERT INTO test_double1(f3,f4) VALUES(123.45,999.995);精度问题CREATE TABLE test_double2( f1 DOUBLE ); INSERT INTO test_double2 VALUES(0.47),(0.44),(0.19); SELECT SUM(f1) FROM test_double2; SELECT SUM(f1) 1.1,1.1 1.1 FROM test_double2;因为浮点数是不准确的所以我们要避免使用“”来判断两个数是否相等定点数的类型用 DECIMAL(M,D) 的方式表示高精度小数。其中M被称为精度D被称为标度。0M650D30DM。例如定义DECIMAL5,2的类型表示该列取值范围是-999.99~999.99。定点数在MySQL内部是以字符串的形式进行存储这就决定了它一定是精准的。CREATE TABLE test_decimal1( f1 DECIMAL, f2 DECIMAL(5,2) ); DESC test_decimal1; INSERT INTO test_decimal1(f1) VALUES(123),(123.45); SELECT * FROM test_decimal1; INSERT INTO test_decimal1(f2) VALUES(999.99); INSERT INTO test_decimal1(f2) VALUES(67.567);#存在四舍五入 #Out of range value for column f2 at row 1 INSERT INTO test_decimal1(f2) VALUES(1267.567); #Out of range value for column f2 at row 1 INSERT INTO test_decimal1(f2) VALUES(999.995);开发经验由于 DECIMAL 数据类型的精准性在开发中除了极少数比如商品编号用到整数类型外其他的数值都用的是 DECIMAL位类型CREATE TABLE test_bit1( f1 BIT, f2 BIT(5), f3 BIT(64) ); DESC test_bit1; INSERT INTO test_bit1(f1) VALUES(0),(1); SELECT * FROM test_bit1; #Data too long for column f1 at row 1 INSERT INTO test_bit1(f1) VALUES(2); INSERT INTO test_bit1(f2) VALUES(31); #Data too long for column f2 at row 1 INSERT INTO test_bit1(f2) VALUES(32); SELECT BIN(f1),BIN(f2),HEX(f1),HEX(f2) FROM test_bit1; #此时0以后可以以十进制的方式显示数据 SELECT f1 0, f2 0 FROM test_bit1;注使用SELECT命令查询位字段时可以用BIN()[二进制]或HEX()[十六进制]函数进行读取。日期与时间类型YEAR类型只需要1个字节的存储空间以4位字符串或数字格式表示YEAR类型其格式为YYYY最小值为1901最大值为2155。以2位字符串格式表示YEAR类型最小值为00最大值为99。当取值为01到69时表示2001到2069当取值为70到99时表示1970到1999当取值整数的0或00添加的话那么是0000年当取值是日期/字符串的0添加的话是2000年。CREATE TABLE test_year( f1 YEAR, f2 YEAR(4) ); DESC test_year; INSERT INTO test_year(f1) VALUES(2021),(2022); SELECT * FROM test_year; INSERT INTO test_year(f1) VALUES (2155); #Out of range value for column f1 at row 1 INSERT INTO test_year(f1) VALUES (2156); INSERT INTO test_year(f1) VALUES (69),(70); INSERT INTO test_year(f1) VALUES (0),(00);DATE类型只需要3个字节的存储空间以 YYYY-MM-DD 格式或者 YYYYMMDD 格式表示的字符串日期其最小取值为1000-01-01最大取值为9999-12-03CREATE TABLE test_date1( f1 DATE ); DESC test_date1; INSERT INTO test_date1 VALUES (2020-10-01), (20201001),(20201001); INSERT INTO test_date1 VALUES (00-01-01), (000101), (69-10-01), (691001), (70-01-01), (700101), (99-01-01), (990101); INSERT INTO test_date1 VALUES (000301), (690301), (700301), (990301); #存在隐式转换 INSERT INTO test_date1 VALUES (CURDATE()),(CURRENT_DATE()),(NOW()); SELECT * FROM test_date1;TIME类型只需要3个字节的存储空间不包含日期使用“HH:MM:SS”格式来表示TIME类型其中HH表示小时MM表示分钟SS表示秒可以使用带有冒号的 字符串比如 D HH:MM:SS 、 HH:MM:SS 、 HH:MM 、 D HH:MM 、 D HH 或 SS 格式都能被正确地插入TIME类型的字段中。其中D表示天其最小值为0最大值为34。如果使用带有D格式的字符串 插入TIME类型的字段时D会被转化为小时计算格式为D*24HH。当使用带有冒号并且不带D的字符串表示时间时表示当天的时间比如12:10表示12:10:00而不是00:12:10CREATE TABLE test_time1( f1 TIME ); DESC test_time1; INSERT INTO test_time1 VALUES(2 12:30:29), (12:35:29), (12:40), (2 12:40),(1 05), (45); INSERT INTO test_time1 VALUES (123520), (124011),(1210); INSERT INTO test_time1 VALUES (NOW()), (CURRENT_TIME()),(CURTIME()); SELECT * FROM test_time1;DATETIME类型需要8个字节的存储空间表示为YYYY-MM-DD HH:MM:SS/YY-MM-DD HH:MM:SS其中YYYY/YY表示年份MM表示月 份DD表示日期HH表示小时MM表示分钟SS表示秒CREATE TABLE test_datetime1( dt DATETIME ); INSERT INTO test_datetime1 VALUES (2021-01-01 06:50:30), (20210101065030); INSERT INTO test_datetime1 VALUES (99-01-01 00:00:00), (990101000000), (20-01-01 00:00:00), (200101000000); INSERT INTO test_datetime1 VALUES (20200101000000), (200101000000), (19990101000000), (990101000000); INSERT INTO test_datetime1 VALUES (CURRENT_TIMESTAMP()), (NOW()),(SYSDATE()); SELECT * FROM test_datetime1;TIMESTAMP类型需要4个字节的存储空间表示为YYYY-MM-DD HH:MM:SS只能存储“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间。其中UTC表示世界统一时间也叫 作世界标准时间CREATE TABLE test_timestamp1( ts TIMESTAMP ); INSERT INTO test_timestamp1 VALUES (1999-01-01 03:04:50), (19990101030405), (99-01-01 03:04:05), (990101030405); INSERT INTO test_timestamp1 VALUES (20200101000000), (200101000000); INSERT INTO test_timestamp1 VALUES (CURRENT_TIMESTAMP()), (NOW()); #Incorrect datetime value INSERT INTO test_timestamp1 VALUES (2038-01-20 03:14:07); SELECT * FROM test_timestamp1;对比DATETIME 和 TIMESTAMPCREATE TABLE temp_time( d1 DATETIME, d2 TIMESTAMP ); INSERT INTO temp_time VALUES(2021-9-2 14:45:52,2021-9-2 14:45:52); INSERT INTO temp_time VALUES(NOW(),NOW()); SELECT * FROM temp_time; #修改当前的时区 SET time_zone 9:00; SELECT * FROM temp_time;两个日期比较大小或日期计算时TIMESTAMP更方便、更快[存放的是毫秒数]。TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同显示不同的结果。而DATETIME则只能 反映出插入时当地的时区其他时区的人查看数据必然会有误差的。开发经验用得最多的日期时间类型就是DATETIME。一般存注册时间、商品发布时间等不建议使用DATETIME存储而是使用时间戳因为 DATETIME虽然直观但不便于计算文本字符串类型CHAR与VARCHAR类型CHAR类型如果保存时数据的实际长度比CHAR类型声明的长度小则会在右侧填充空格以达到指定的长 度。当MySQL检索CHAR类型的数据时CHAR类型的字段会去除尾部的空格。CREATE TABLE test_char1( c1 CHAR, c2 CHAR(5) ); INSERT INTO test_char1(c1) VALUES(a); #Data too long for column c1 at row 1 INSERT INTO test_char1(c1) VALUES(ab);#默认情况下是一个字符 INSERT INTO test_char1(c2) VALUES(ab); INSERT INTO test_char1(c2) VALUES(hello); INSERT INTO test_char1(c2) VALUES(尚); INSERT INTO test_char1(c2) VALUES(硅谷); INSERT INTO test_char1(c2) VALUES(尚硅谷教育); #Data too long for column c2 at row 1 INSERT INTO test_char1(c2) VALUES(尚硅谷IT教育); SELECT * FROM test_char1; SELECT CONCAT(c2,***) FROM test_char1; INSERT INTO test_char1(c2) VALUES(ab ); SELECT CHAR_LENGTH(c2) FROM test_char1;VARCHAR类型VARCHAR(M) 定义时必须指定长度Mvarchar(20)指的是20字符检索VARCHAR类型的字段数据时会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节[记录长度]。CREATE TABLE test_varchar1( NAME VARCHAR #错误必须指定字符个数 ); #Column length too big for column name (max 21845); use BLOB or TEXT instead CREATE TABLE test_varchar2( NAME VARCHAR(65535)#使用utf8字符集每一个字符占3个字节 );CREATE TABLE test_varchar3( NAME VARCHAR(5) ); INSERT INTO test_varchar3 VALUES(尚硅谷),(尚硅谷教育); #Data too long for column NAME at row 1 INSERT INTO test_varchar3 VALUES(尚硅谷IT教育);存储很短的信息[VARCHAR需要额外字节保留长度]固定长度的信息[VARCHAR的动态性需求消失]十分频繁改变的字段[VARCHAR需要额外时间运算消耗增加]--CHAR不同存储引擎下MyISAM:推荐使用CHAR整个表静态化检索效率更高MEMORY:均可在处理时均作为CHAR类型处理InnoDB:推荐使用VARCHAR内部的行存储格式并没有区分固定长度和可变长度列而且主要影响性能的因素是数据行使用的存储总量TEXT类型其实际存储长度不确定在MySQL中不允许作为主键CREATE TABLE test_text( tx TEXT ); INSERT INTO test_text VALUES(atguigu ); SELECT CHAR_LENGTH(tx) FROM test_text; #10ENUM类型只允许从成员中选取单个值不能一次选取多个值CREATE TABLE test_text( tx TEXT ); INSERT INTO test_text VALUES(atguigu ); SELECT CHAR_LENGTH(tx) FROM test_text; #10 #8. ENUM类型 CREATE TABLE test_enum( season ENUM(春,夏,秋,冬,unknow) ); INSERT INTO test_enum VALUES(春),(秋); SELECT * FROM test_enum; #Data truncated for column season at row 1 INSERT INTO test_enum VALUES(春,秋); #Data truncated for column season at row 1 INSERT INTO test_enum VALUES(人); INSERT INTO test_enum VALUES(unknow); #忽略大小写的 INSERT INTO test_enum VALUES(UNKNOW); #可以使用索引进行枚举元素的调用 INSERT INTO test_enum VALUES(1),(3); # 没有限制非空的情况下可以添加null值 INSERT INTO test_enum VALUES (NULL);SET类型SET表示一个字符串对象可以包含0个或多个成员但成员个数的上限为64。设置字段值时可以取取值范围内的 0个或多个值CREATE TABLE test_set( s SET (A, B, C) ); INSERT INTO test_set (s) VALUES (A), (A,B); #插入重复的SET类型成员时MySQL会自动删除重复的成员 INSERT INTO test_set (s) VALUES (A,B,C,A); #向SET类型的字段插入SET成员中不存在的值时MySQL会抛出错误。 INSERT INTO test_set (s) VALUES (A,B,C,D); SELECT * FROM test_set;二进制字符串类型存储一些二进制数据比如可以存储图片、音频和视频等二进制数据BINARY与VARBINARY类型BINARY和VARBINARY类似于CHAR和VARCHAR只是它们存储的是二进制字符串。CREATE TABLE test_binary1( f1 BINARY, f2 BINARY(3), #f3 VARBINARY, f4 VARBINARY(10) ); DESC test_binary1; INSERT INTO test_binary1(f1,f2) VALUES(a,abc); SELECT * FROM test_binary1; #Data too long for column f1 at row 1 INSERT INTO test_binary1(f1) VALUES(ab); INSERT INTO test_binary1(f2,f4) VALUES(ab,ab); SELECT LENGTH(f2),LENGTH(f4) FROM test_binary1;BLOB类型BLOB是一个 二进制大对象 可以容纳可变数量的数据。可以存储一个二进制的大对象比如图片 、 音频 和 视频等。CREATE TABLE test_blob1( id INT, img MEDIUMBLOB ); INSERT INTO test_blob1(id) VALUES (1001); SELECT * FROM test_blob1;JSON类型JSONJavaScript Object Notation是一种轻量级的 数据交换格式。JSON 可以将 JavaScript 对象中表示的一组数据转换为字符串然后就可以在网络或者程序之间轻松地传递这个字符串并在需要的时候将它还原为各编程语言所支持的数据格式。CREATE TABLE test_json( js json ); INSERT INTO test_json (js) VALUES ({name:songhk, age:18, address:{province:beijing, city:beijing}}); SELECT * FROM test_json; SELECT js - $.name AS NAME,js - $.age AS age ,js - $.address.province AS province, js - $.address.city AS city FROM test_json;开发选择在定义数据类型时如果确定是整数就用 INT如果是小数一定用定点数类型DECIMAL(M,D)[必须使用超过议将数据拆成整数和小数并分开存储]如果是日期与时间就用DATETIME字符串长度几乎相等使用CHAR 定长字符串类型VARCHAR 是可变长字符串不预先分配存储空间长度不要超过 5000。存储长度大于此值定义字段类型为 TEXT独立出来一张表用主键来对应避免影响其它字段索引效率。