数据类型
#关于属性:character set name
SHOW VARIABLES LIKE 'character_%';#查看mysql字符集
#创建数据库时指明字符集
CREATE DATABASE IF NOT EXISTS dbtest12 CHARACTER SET 'utf8';
#创建表时,指明表的字符集
CREATE TABLE IF NOT EXISTS temp(
id INT
)CHARACTER SET 'utf8';
#创建表,指明表中的字段时,可以指定字段的字符集
CREATE TABLE temp1(
id INT,
NAME VARCHAR(5) CHARACTER SET 'gbk'
);
-
整数类型:
值范围无关 。从MySQL 8.0.17开始,整数数据类型不推荐使用显示宽度属性。
适用场景:
①TINYINT :一般用于枚举数据,比如系统设定取值范围很小且固定的场景。
②SMALLINT :可以用于较小范围的统计数据,比如统计工厂的固定资产库存数量等。
③MEDIUMINT :用于较大整数的计算,比如车站每日的客流量等。
④INT、INTEGER :取值范围足够大,一般情况下不用考虑超限问题,用得最多。比如商品编号。
⑤BIGINT :只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、
证 券公司衍生产品持仓等。
#整数类型
CREATE TABLE test_int2(
f1 INT,
f2 INT(5),
f3 INT(5) ZEROFILL #显示宽度为5,当insert的值不足5位时,使用0填充。超过5位照常显示,当使用zerofill时,自动添加unsigned
)
UNSIGNED : 无符号类型(非负):
①所有的整数类型都有一个可选的属性UNSIGNED(无符号属性),无符号整
数类型的最小取值为0。所以,如果需要在MySQL数据库中保存非负整数值时,
可以将整数类型设置为无符号类型。
②int类型默认显示宽度为int(11),无符号int类型默认显示宽度为int(10)。
CREATE TABLE test_int3(
f1 INT UNSIGNED );
mysql> desc test_int3;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| f1 | int(10) unsigned | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
-
浮点类型:
浮点数和定点数类型的特点是可以 处理小数 ,你可以把整数看成小数的一个特例。
因此,浮点数和定点数的使用场景,比整数大多了。 MySQL支持的浮点数类型,
分别是 FLOAT、 DOUBLE、REAL。
①FLOAT 占用字节数少,取值范围小;DOUBLE 占用字节数多,取值范围也大。
数据精度说明:
①在MySQL中单精度值使用 4 个字节,双精度值使用 8 个字节。
②从MySQL 8.0.17开始,FLOAT(M,D) 和DOUBLE(M,D)用法在官方文档中已经明确不推荐使用
浮点数类型有个缺陷,就是不精准。下面我来重点解释一下为什么 MySQL 的浮点数不够精准。
#浮点类型:
CREATE TABLE test_doublel(
f1 FLOAT,
f2 FLOAT(5,2),
f3 DOUBLE,
f4 DOUBLE(5,2)
)
INSERT INTO test_doublel(f1,f2)
VALUES (123.45,123.456);
INSERT INTO test_doublel(f3,f4)
VALUES (123.45,124.456); #存在四舍五入
SELECT * FROM test_doublel;
定点数类型:
浮点数 vs 定点数:
①浮点数相对于定点数的优点是在长度一定的情况下,浮点类型取值范围大,但是不精准,
适用于需要取值范围大,又可以容忍微小误差的科学计算场景(比如计算化学、分子建模、
流体动力学等)。
②定点数类型取值范围相对小,但是精准,没有误差,适合于对精度要求极高的场景
(比如涉 及金额计算的场景)。
#定点数类型
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;
DESC test_decimal1;
INSERT INTO test_decimal1(f2)
VALUES (67.567);#存在四舍五入
开发中经验:
“由于 DECIMAL 数据类型的精准性,在我们的项目中,除了极少数(比如商品编号)
用到整数类型外,其他的数值都用的是 DECIMAL,原因就是这个项目所处的零售行业,
要求精准,一分钱也不能差。
-
位类型BIT:
BIT类型中存储的是二进制值,类似010110。
-
日期与时间类型:
①使用 CURRENT_DATE() 或者 NOW() 函数,会插入当前系统的日期。
②从MySQL5.5.27开始,2位格式的YEAR已经不推荐使用。
YEAR默认格式就是“YYYY”,没必要写成YEAR(4),
③从MySQL 8.0.19开始,不推荐使用指定显示宽度的YEAR(4)数据类型。
#DATE类型:
CREATE TABLE test_date1(
f1 DATE );
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 (CURRENT_DATE()),(NOW());
SELECT * FROM test_date1;
#TIME
CREATE TABLE test_time1(
f1 TIME
);
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());
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());
/*
TIMESTAMP类型:
存储数据的时候需要对当前时间所在的时区进行转换,查询数据的时候再将时间转换回当前的时区。
因此,使用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 VALUES ('2038-01-20 03:14:07');
开发中经验:
用得最多的日期时间类型,就是 DATETIME 。虽然 MySQL 也支持 YEAR(年)、 TIME(时间)、
DATE(日期),以及 TIMESTAMP 类型,但是在实际项目中,尽量用 DATETIME 类型。
-
文本字符串类型:
字符串总体上分为 CHAR 、 VARCHAR 、 TINYTEXT 、 TEXT 、 MEDIUMTEXT 、
LONGTEXT 、 ENUM 、 SET 等类型。
CHAR类型:
①CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M),则表示长度默认是1个字符。
②如果保存时,数据的实际长度比CHAR类型声明的长度小,则会在 右侧填充 空格以达到指定的长
度。当MySQL检索CHAR类型的数据时,CHAR类型的字段会去除尾部的空格。
③定义CHAR类型字段时,声明的字段长度即为CHAR类型字段所占的存储空间的字节数。
CREATE TABLE test_char1( c1 CHAR, c2 CHAR(5) );
DESC test_char1;
INSERT INTO test_char1
VALUES('a','Tom');
SELECT c1,CONCAT(c2,'***') FROM test_char1;
INSERT INTO test_char1(c2)
VALUES('a ');
SELECT CHAR_LENGTH(c2) FROM test_char1;
VARCHAR类型:
VARCHAR(M) 定义时, 必须指定 长度M,否则报错。(每个汉字3字节)
CREATE TABLE test_varchar1(
NAME VARCHAR #错误
);
#Column length too big for column 'NAME' (max = 21845);
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思想和过程');
ENUM类型:
① ENUM类型只允许从成员中选取单个值,不能一次选取多个值。
②其所需要的存储空间由定义ENUM类型时指定的成员个数决定。
CREATE TABLE test_enum(
season ENUM('春','夏','秋','冬','unknow')
);
INSERT INTO test_enum
VALUES('春'),('秋');
# 忽略大小写
INSERT INTO test_enum
VALUES('UNKNOW');
# 允许按照角标的方式获取指定索引位置的枚举值
INSERT INTO test_enum
VALUES('1'),(3);
# Data truncated for column 'season' at row 1
INSERT INTO test_enum
VALUES('ab');
# 当ENUM类型的字段没有声明为NOT NULL时,插入NULL也是有效的
INSERT INTO test_enum
VALUES(NULL);
SET类型:
SET表示一个字符串对象,可以包含0个或多个成员,但成员个数的上限为 64 。
设置字段值时,可以取取值范围内的 0 个或多个值。
注意:SET类型在选取成员时,可以一次选择多个成员,这一点与ENUM类型不同。
-
二进制字符串类型: