MySQL之数据类型精讲

#MySQL数据类型的精讲
#关于属性：character set name

SHOW VARIABLES LIKE 'character_%';

#创建数据库时指明字符集
CREATE DATABASE IF NOT EXISTS dbtest12 CHARACTER SET 'utf8';

SHOW CREATE DATABASE dbtest12;

#创建表的时候指明表的字符集
CREATE TABLE temp(
id INT
)CHARACTER SET 'utf8';

SHOW CREATE TABLE temp;

#创建表指明表中的字段时，可以指定字段的字符集
CREATE TABLE tmp1(
id INT,
NAME VARCHAR(15) CHARACTER SET 'gbk'

);
SHOW CREATE TABLE tmp1;

#整形的数据类型
USE dbtest12;

CREATE TABLE test_int1(
f1 TINYINT,
f2 SMALLINT,
f3 MEDIUMINT,
f4 INTEGER,
f5 BIGINT

);
DESC test_int1;

INSERT INTO test_int1(f1)
VALUES
(12),
(-12),
(-128),
(127);

SELECT f1
FROM test_int1
#Out of range value for column 'f1' at row 1
INSERT INTO test_int1(f1)
VALUES(128);

CREATE TABLE IF NOT EXISTS 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);

INSERT INTO test_int2(f3)
VALUES(123);

SELECT *
FROM test_int2;

SHOW CREATE TABLE test_int2;

CREATE TABLE test_int3(
f1 INT UNSIGNED #无符号整型
);

DESC test_int3;

INSERT INTO test_int3
VALUES(2412321);

SELECT *
FROM test_int3;

#Out of range value for column 'f1' at row 1
INSERT INTO test_int3
VALUES(4294967296);

#浮点类型
CREATE TABLE test_double(
f1 FLOAT,
f2 FLOAT(5,2),
f3 DOUBLE,
f4 DOUBLE(5,2)#保留两位小数
);

DESC test_double;

INSERT INTO test_double(f1,f2)
VALUES
(123.45,123.45);

SELECT *FROM test_double

INSERT INTO test_double(f3,f4)
VALUES
(123.45,123.456);#如果小数位超出指定范围   四舍五入 进位

SELECT *
FROM test_double

#Out of range value for column 'f4' at row 1
INSERT INTO test_double(f3,f4)
VALUES
(123.45,1234.456);

#Out of range value for column 'f4' at row 1
INSERT INTO test_double(f3,f4)
VALUES
(123.45,999.995);#因为四舍五入 进位 超过取值范围

#测试FLOAT和DOUBLE的精度问题
CREATE TABLE test_double2(
f1 DOUBLE
);

INSERT INTO test_double2
VALUES(0.47),(0.44),(0.19);

SELECT * FROM test_double2

SELECT SUM(f1)
FROM test_double2;

SELECT SUM(f1) =1.1,1.1=1.1 #存在精度问题
FROM test_double2;

#定点数类型
CREATE TABLE decimal1(
f1 DECIMAL,
f2 DECIMAL(5,2)
);

DESC decimal1; 

INSERT INTO decimal1(f1)
VALUES
(123),
(123.45);#四舍五入了

SELECT * FROM decimal1;

INSERT INTO decimal1(f2)
VALUES
(999.99);

INSERT INTO decimal1(f2)
VALUES
(67.567);#四舍五入

SELECT * FROM decimal1

#超过3位报错 Out of range value for column 'f2' at row 1
INSERT INTO decimal1(f2)
VALUES(1267.567);

#进位超出范围 Out of range value for column 'f2' at row 1
INSERT INTO decimal1(f2)
VALUES(999.995);

#演示decimal 去替换 double

ALTER TABLE test_double2
MODIFY f1 DECIMAL(5,2);

DESC test_double2;


SELECT SUM(f1)
FROM test_double2;

SELECT SUM(f1) =1.1,1.1=1.1 #存在精度问题
FROM test_double2;#定点数的精准

SELECT * FROM test_double

#位类型 bit
CREATE TABLE test_bit1(
f1 BIT,#没有指定值时默认为1
f2 BIT(5),
f3 BIT(64)
);

DESC test_bit1;

INSERT INTO test_bit1(f1)
VALUES(1),(0);

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 HEX(f2),HEX(f2)
FROM test_bit1;

SELECT BIN(f1),BIN(f1)
FROM test_bit1;

#此时+0可以以十进制的方式显示数据
SELECT f1+0,f2+0
FROM test_bit1;

#YEAR类型
CREATE TABLE test_year(
f1 YEAR,
f2 YEAR(4)
);

DESC test_year;

INSERT INTO test_year(f1)
VALUES('2021'),(2022);

SELECT * FROM test_year;

#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');#0 是0000年  '0'是2000

#DATE类型
CREATE TABLE test_date1(
f1 DATE
);

SELECT *
FROM test_date1;

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类型
CREATE TABLE test_time1(
f1 TIME
);

DESC test_time1;

INSERT INTO test_time1#D会被转化为小时，计算格式为D*24+HH D表示的是天数
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());

INSERT INTO test_time1
VALUES(CURTIME());

SELECT * FROM test_time1;

# DATETIME类型

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());

SELECT *
FROM test_datetime1

/*
TIMESTAMP和DATETIME的区别：
TIMESTAMP存储空间比较小，表示的日期时间范围也比较小只能存储“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间。
底层存储方式不同，TIMESTAMP底层存储的是毫秒值，距离1970-1-1 0:0:0 0毫秒的毫秒值。
两个日期比较大小或日期计算时，TIMESTAMP更方便、更快。
TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同，显示不同的结果。而DATETIME则只能
反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。
*/

#TIMESTAMP类型
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 ('2020@01@01@00@00@00'), ('20@01@01@00@00@00');

INSERT INTO test_timestamp1
VALUES (CURRENT_TIMESTAMP()), (NOW());

#Incorrect datetime value 超出指定范围
INSERT INTO test_timestamp1#Incorrect datetime value: '2038-01-20 03:14:07' for column 'ts' at row 1
VALUES ('2038-01-20 03:14:07');

SELECT * FROM test_timestamp1; 

#对比DATETIME 和 TIMESATMP

/*存储数据的时候需要对当前时间所在的时区进行转换，查询数据的时候再将时间转换回当前的时区。
因此,使用TIMESTAMP存储的同一个时间值,在不同的时区查询时会显示不同的时间.*/

CREATE TABLE temp_time(
d1 DATETIME,
d2 TIMESTAMP
);

INSERT INTO temp_time 
VALUES('2021-5-2 10:15:52','2021-5-2 10:15:52');

INSERT INTO temp_time VALUES(NOW(),NOW()); 

SELECT * FROM temp_time;

#修改当前的时区
SET time_zone='+9:00';
SELECT * FROM temp_time; 
#获取当前时间戳
SELECT UNIX_TIMESTAMP();


#文本字符串类型

 #CHAR类型
 CREATE TABLE test_char1(
c1 CHAR,
c2 CHAR(5)
);

DESC test_char1;

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教育');

/*
CHAR类型：
CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M)，则表示长度默认是1个字符。
如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在 右侧填充 空格以达到指定的长
度。当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格。
定义CHAR类型字段时，声明的字段长度即为CHAR类型字段所占的存储空间的字节数。
*/

SELECT CONCAT(c2,'***')
FROM test_char1;

SELECT * FROM test_char1;

INSERT INTO Test_char1(c2)
VALUES('ab  ');

SELECT CONCAT(c2,'***')
FROM test_char1;

SELECT CHAR_LENGTH(c2)
FROM test_char1;
 
#varchar类型
/*
VARCHAR类型：
VARCHAR(M) 定义时， 必须指定 长度M，否则报错。
MySQL4.0版本以下，varchar(20)：指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字
节） ；MySQL5.0版本以上，varchar(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)
);

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教育');

SELECT * FROM test_varchar3;

#TEXT类型
/*
在MySQL中，TEXT用来保存文本类型的字符串，总共包含4种类型，分别为TINYTEXT、TEXT、
MEDIUMTEXT 和 LONGTEXT 类型。
*/
#由于实际存储的长度不确定，MySQL 不允许 TEXT 类型的字段做主键

CREATE TABLE test_text(
tx TEXT
);

INSERT INTO test_text
VALUES('atguigu ');

SELECT CHAR_LENGTH(tx)
FROM test_text; #8 不会去除空格

#ENUM类型

CREATE TABLE test_enum(
season ENUM('春','夏','秋','冬','unknow')
);

INSERT INTO test_enum
VALUES('春'),('夏')

SELECT *
FROM test_enum;

#错误的 Column count doesn't match value count 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类型
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会抛出错误。Data truncated for column 's' at row 1
INSERT INTO test_set (s) VALUES ('A,B,C,D');

SELECT *
FROM test_set;

CREATE TABLE temp_mul(
gender ENUM('男','女'),
hobby SET('吃饭','睡觉','打豆豆','写代码')
);

INSERT INTO temp_mul
VALUES('男','睡觉,写代码');

SELECT * FROM temp_mul;

#错误 Data truncated for column 'gender' at row 1
INSERT INTO temp_mul
VALUES('男,女','睡觉,写代码');

#BINARY 与 VARBINARY类型

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;  #(f2)length为3固定长度 (f4)length为2


#BLOB类型
#BLOB是一个 二进制大对象 ，可以容纳可变数量的数据。
/*
MySQL中的BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB 4种类型，它们可容纳值的最大
长度不同。可以存储一个二进制的大对象，比如 图片 、 音频 和 视频 等。
需要注意的是，在实际工作中，往往不会在MySQL数据库中使用BLOB类型存储大对象数据，通常会将图
片、音频和视频文件存储到 服务器的磁盘上 ，并将图片、音频和视频的访问路径存储到MySQL中。
*/
CREATE TABLE test_blob1(
id INT,
img MEDIUMBLOB
);

INSERT INTO test_blob1(id)
VALUES(1001);

SELECT * FROM test_blob1;

#JSON 类型

CREATE TABLE test_json(
js json
);


INSERT INTO test_json (js)
VALUES ('{"name":"zend", "age":20, "address":{"province":"beijing","city":"beijing"}}');

SELECT * FROM test_json;

#mysql中如何提取json中的值
SELECT js -> '$.name' AS NAME,js -> '$.age' AS age ,js -> '$.address.province'
AS province, js -> '$.address.city' AS city FROM test_json;

#空间类型
/*
MySQL的空间数据类型（Spatial Data Type）对应于OpenGIS类，包括单值类型：GEOMETRY、POINT、
LINESTRING、POLYGON以及集合类型：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、
GEOMETRYCOLLECTION 。
*/
/*
13. 小结及选择建议
在定义数据类型时，如果确定是 整数 ，就用 INT ； 如果是 小数 ，一定用定点数类型
DECIMAL(M,D) ； 如果是日期与时间，就用 DATETIME 。这样做的好处是，首先确保你的系统不会因为数据类型定义出错。不过，凡事都是有两面的，可靠性好，并不意味着高效。比如，TEXT 虽然使用方便，但是效率不如 CHAR(M) 和 VARCHAR(M)。
关于字符串的选择，
建议参考如下阿里巴巴的《Java开发手册》规范：
阿里巴巴《Java开发手册》之MySQL数据库：
任何字段如果为非负数，必须是 UNSIGNED
【 强制 】小数类型为 DECIMAL，禁止使用 FLOAT 和 DOUBLE。
说明：在存储的时候，FLOAT 和 DOUBLE 都存在精度损失的问题，很可能在比较值的时候，得
到不正确的结果。如果存储的数据范围超过 DECIMAL 的范围，建议将数据拆成整数和小数并
分开存储。
【 强制 】如果存储的字符串长度几乎相等，使用 CHAR 定长字符串类型。
【 强制 】VARCHAR 是可变长字符串，不预先分配存储空间，长度不要超过 5000。如果存储长度大
于此值，定义字段类型为 TEXT，独立出来一张表，用主键来对应，避免影响其它字段索引效率。
*/

#课后练习
#1. 掌握MySQL规范的各种数据类型
/*
整数类型一共有 5 种，包括 TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）和 BIGINT。

MySQL支持的浮点数类型，分别是 FLOAT、DOUBLE、REAL。对于浮点类型，在MySQL中单精度值使用 4 个字节，双精度值使用 8 个字节。

MySQL中的定点数类型只有 DECIMAL 一种类型。

BIT类型中存储的是二进制值，类似010110。

日期与时间类型
YEAR 类型通常用来表示年
DATE 类型通常用来表示年、月、日
TIME 类型通常用来表示时、分、秒
DATETIME 类型通常用来表示年、月、日、时、分、秒
TIMESTAMP 类型通常用来表示带时区的年、月、日、时、分、秒

YEAR类型
YEAR类型用来表示年份，在所有的日期时间类型中所占用的存储空间最小，只需要 1个字节 的存储空间。

DATE类型
DATE类型表示日期，没有时间部分，格式为 YYYY-MM-DD ，其中，YYYY表示年份，MM表示月份，DD表示
日期。需要 3个字节 的存储空间。在向DATE类型的字段插入数据时，同样需要满足一定的格式条件。

TIME类型
TIME类型用来表示时间，不包含日期部分。在MySQL中，需要 3个字节 的存储空间来存储TIME类型的数
据，可以使用“HH:MM:SS”格式来表示TIME类型，其中，HH表示小时，MM表示分钟，SS表示秒。

DATETIME类型
DATETIME类型在所有的日期时间类型中占用的存储空间最大，总共需要 8 个字节的存储空间。在格式上
为DATE类型和TIME类型的组合，可以表示为 YYYY-MM-DD HH:MM:SS ，其中YYYY表示年份，MM表示月
份，DD表示日期，HH表示小时，MM表示分钟，SS表示秒。

 TIMESTAMP类型
TIMESTAMP类型也可以表示日期时间，其显示格式与DATETIME类型相同，都是 YYYY-MM-DD
HH:MM:SS ，需要4个字节的存储空间。但是TIMESTAMP存储的时间范围比DATETIME要小很多，只能存储
“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间。其中，UTC表示世界统一时间，也叫
作世界标准时间。

MySQL中，文本字符串总体上分为 CHAR 、 VARCHAR 、 TINYTEXT 、 TEXT 、 MEDIUMTEXT 、
LONGTEXT 、 ENUM 、 SET 等类型。

具体存储引擎中的情况：
MyISAM 数据存储引擎和数据列：MyISAM数据表，最好使用固定长度(CHAR)的数据列代替可变长
度(VARCHAR)的数据列。这样使得整个表静态化，从而使 数据检索更快 ，用空间换时间。
MEMORY 存储引擎和数据列：MEMORY数据表目前都使用固定长度的数据行存储，因此无论使用
CHAR或VARCHAR列都没有关系，两者都是作为CHAR类型处理的。
InnoDB 存储引擎，建议使用VARCHAR类型。因为对于InnoDB数据表，内部的行存储格式并没有区
分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），而且主要影响性能的因素
是数据行使用的存储总量，由于char平均占用的空间多于varchar，所以除了简短并且固定长度的，
其他考虑varchar。这样节省空间，对磁盘I/O和数据存储总量比较好。

MySQL中支持的二进制字符串类型主要包括BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和
LONGBLOB类型。

BLOB是一个 二进制大对象 ，可以容纳可变数量的数据。
MySQL中的BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB 4种类型，它们可容纳值的最大
长度不同。可以存储一个二进制的大对象，比如 图片 、 音频 和 视频 等。


JSON 类型
JSON（JavaScript Object Notation）是一种轻量级的 数据交换格式 。简洁和清晰的层次结构使得 JSON 成
为理想的数据交换语言。它易于人阅读和编写，同时也易于机器解析和生成，并有效地提升网络传输效
率。JSON 可以将 JavaScript 对象中表示的一组数据转换为字符串，然后就可以在网络或者程序之间轻
松地传递这个字符串，并在需要的时候将它还原为各编程语言所支持的数据格式。

MySQL 空间类型扩展支持地理特征的生成、存储和分析。这里的地理特征表示世界上具有位置的任何东
西，可以是一个实体，例如一座山；可以是空间，例如一座办公楼；也可以是一个可定义的位置，例如
一个十字路口等等。MySQL中使用 Geometry（几何） 来表示所有地理特征。Geometry指一个点或点的
集合，代表世界上任何具有位置的事物。



*/
#2. 熟悉数据类型常设置的属性
#略 具体请参考 尚硅谷MySQL宋红康版


#3. 掌握不同的类型的使用场景
/*
TINYINT ：一般用于枚举数据，比如系统设定取值范围很小且固定的场景。
SMALLINT ：可以用于较小范围的统计数据，比如统计工厂的固定资产库存数量等。
MEDIUMINT ：用于较大整数的计算，比如车站每日的客流量等。
INT、INTEGER ：取值范围足够大，一般情况下不用考虑超限问题，用得最多。比如商品编号。
BIGINT ：只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等。
*/
#略 具体请参考 尚硅谷MySQL宋红康版