基本数据类型
【一】整形
- 整形默认带符号，如果希望使用无符号，可以设置约束条件
unsigned
- 一个字节代表8位 11111111
| 整数类型 |
字节 |
无符号数的取值范围 |
有符号数的取值范围 |
| TINYINT |
1 |
0~255 |
-128~127 |
| SMALLINT |
2 |
0~65535 |
-32768~32767 |
| MEDIUMINT |
3 |
0~16777215 |
-8388608~8388607 |
| INT |
4 |
0~4294967295 |
-2147483648~2147483647 |
| BIGINT |
8 |
0~18446744073709551615 |
-9223372036854774808~9223372036854774807 |
-- 创建演示表
-- 全部不指定位数，使用默认位数
create table t1(
-- tinyint
tiny_int tinyint,
tiny_int_unsign tinyint unsigned
);
create table t2(
-- smallint
small_int smallint,
small_int_unsign smallint unsigned
);
create table t3(
-- mediumint
medium_int mediumint,
medium_int_unsign mediumint unsigned
);
create table t4(
-- int
int_type int,
int_type_unsign int unsigned
);
create table t5(
-- bigint
big_int bigint,
big_int_unsign bigint unsigned
);
【1】TINYINT
-- 测试数据
# tinyint
insert into t1(tiny_int) values(256); -- 超出取值范围
insert into t1(tiny_int) values(128); -- 超出取值范围,因为不指定unsign时，最大取值范围为-128~127
# tinyint unsigned
insert into t1(tiny_int_unsign) values(128);
insert into t1(tiny_int_unsign) values(-128); -- 超出范围，因为设置了无符号后，最大范围为0~255
【2】SMALLINT
insert t2 values(-32768,0);
insert t2 values(32767,65535);
【3】MEDIUMINT
insert t3 values(-8388608,0);
insert t3 values(8388607,16777215);
【4】INT
insert t4 values(-2147483648,0);
insert t4 values(2147483647,4294967295);
【5】BIGINT
insert t5 values(-9223372036854774808,0);
insert t5 values(9223372036854774807,18446744073709551615);
【6】整形数据的指定宽度
- 当你指定整数类型的字段宽度时，它会影响显示的宽度，但不会改变数据的存储方式或范围。
# 通过约束条件zerofill来展示
create table t6(int_width tinyint(3) unsigned zerofill);
-- 插入数据
insert t6 values(255);
insert t6 values(24);
insert t6 values(2);
- 当指定宽度后，哪怕超出整数类型【TINYINT】的最大取值范围后，并不会报错，但是会被变成最大值
- 注：目前测下来只有tinyint是不会报错，其他整数类型均会报错
create table t7(int_width int(5) zerofill);
insert t7 values(9999999);
insert t7 values(255);
insert t7 values(4294967300);
-- Out of range value for column 'int_width' at row 1
【二】浮点型
| 数据类型 |
字节数 |
取值范围 |
| FLOAT |
4 |
-2^128~2 ^128，即-3.40E+38~+3.40E+38 |
| DOUBLE |
8 |
-2^1024~ 2^1024，即-1.79E+308~1.79E+308 |
| DECIMAL |
设置位数和精度。 |
65 ~ 30 |
create table t1(
float_type float,
double_type double,
decimal_type decimal
);
-
对于浮点数而言，字段类型后的宽度，有两个参数，限制的是(最大的位数，小数位数占总位数的多少位)
insert t1(float_type,double_type,decimal_type)
values(
1.111111111111111111111111,
1.111111111111111111111111,
1.111111111111111111111111
-- 插入同样的值
);
-- 最大值
float（255，30）：总位数255，小数点后30位
double（60，30）：总位数60，小数点后30位
decimal（60，30）：总位数60，小数点后30位
-
三者的区别在于精度的区别
- FLOAT:
- 单精度浮点数，占用 4 个字节。
- 精度为大约 7 位小数。
- DOUBLE:
- 双精度浮点数，占用 8 个字节。
- 精度为大约 15 位小数。
- DECIMAL
- DECIMAL 类型不会有精度损失问题，因为它精确地存储指定位数的小数。
- 需要注意的是，DECIMAL 类型在存储和计算上通常会占用更多的存储空间和计算资源
-- 设置最大位数，展示精度的区别
create table t3(
float_type float(255,30),
double_type double(60,30),
decimal_type decimal(60,30)
);
insert t2(float_type,double_type,decimal_type)
values(
1.111111111111111111111111,
1.111111111111111111111111,
1.111111111111111111111111
-- 插入同样的值
);
-
一定注意，浮点数的指定宽度的第一个参数是总位数
- 如decimal(5,2)，意为总位数5位，其中2位是小数点
- 最大的值为999.99
【三】字符串
【1】char
- 定长类型 超出位数就报错 如果位数不够 使用空格填充
【2】varchar
- 不定长类型 超出位数报错 不够5位的 有几位存几位
【3】验证是否补充了空格
- 默认情况下，没有对char类型填充空格，如果想看填充了空格，需要设置严格模式
- PAD_CHAR_TO_FULL_LENGTH
- SQL语句：
set session sql_mode='*,PAD_CHAR_TO_FULL_LENGTH'
-- 创建表
create table t1(
str1 char(6),-- char类型默认宽度为1
str2 varchar(6) -- varchar类型必须填写宽度，否则将报错
);
-- 插入数据
insert t1(str1,str2) values('abc','abc');
select * from t1;
-- 通过char_length关键字得到字符长度
select char_length(str1) from t1;
select char_length(str2) from t1;
-- 遇到的问题
create table t1(
str1 char(6),-- char类型默认宽度为1
str2 varchar(6) -- varchar类型必须填写宽度，否则将报错
);
insert t1 value('你好','你好'); -- 正常插入
insert t1 value('你好'); -- Column count doesn't match value count at row 1
为什么第二个插入语句会报错？
# 在 SQL 中，插入语句的格式通常是 INSERT INTO 表名 (列1, 列2, 列3, ...) VALUES (值1, 值2, 值3, ...);。当你省略列名时，系统期望你提供与表中列数量相匹配的值。
# 可以通过指定字段传值
insert t1(str1) vaule('你好') -- 就不会报错了
【四】日期
- date：年月日
- datetime：年月日 时分秒
- 'YYYY-MM-DD HH:MM:SS'
- 2000-1-2 11:11:11
- timestamp：时间戳
- 与
DATETIME 类似，但在插入或更新时会自动转换为当前时区。
- time：时分秒
- year：年
-- 建表
create table t2(
date_type date,
datetime_t datetime,
timestamp_t timestamp,
time_t time,
year_t year
);
-- 传入数据
# 传入数据必须按照格式填写否则将会报错
insert t2(date_type,datetime_t,timestamp_t,time_t,year_t) values
('2001-1-1','2001-1-1 11:11:11','2001-1-1 11:11:11','11:11:11',2001)
-- year 类型 传入数字或字符均可'2001'
;
-- 错误情况
insert t2(date_type,datetime_t,timestamp_t,time_t,year_t) values
('2001/1/1','2001年1月1日 11:11:11','2001-1-1 11:11:11','11:11:11',2001)
;
【五】枚举
【1】enum：多选一
-- 建表
create table t1(
str enum('a','b','c')
);
-- 传入数据
insert t1(str) value('a');
insert t1(str) value('a,b'); -- ERROR 1265 (01000): Data truncated for column 'str' at row 1
# 只能传一个值
【2】set：多选多
-- 建表
create table t2(
str set('a','b','c')
);
-- 传入数据
insert t2(str) value('a');
insert t2(str) value('a,b');
insert t2(str) value('a,b,c');
insert t2(str) value('a,b,b'); -- 重复填写不会报错，但并不会保存重复值
【六】严格模式
【1】查看严格模式
-- 查看全局严格模式
SELECT @@GLOBAL.sql_mode;
-- 查看当前会话的严格模式
SELECT @@SESSION.sql_mode;
-- 显示所有变量名中包含 "mode" 的系统变量及其对应的值
-- 会有sql_mode
show variables like "%mode";
【2】常见的严格模式介绍
- STRICT_TRANS_TABLES:
- 当插入或更新数据时，对事务安全表（支持事务的表）进行更严格的数据验证。
- STRICT_ALL_TABLES:
- NO_ZERO_DATE:
- NO_ZERO_IN_DATE:
- ERROR_FOR_DIVISION_BY_ZERO:
- NO_AUTO_CREATE_USER:
- ONLY_FULL_GROUP_BY:
- 要求
GROUP BY 子句中的列必须是 SELECT 列的子集，而不是任意表达式。
- NO_ENGINE_SUBSTITUTION:
- 如果指定的存储引擎不可用，不要尝试使用其他存储引擎，而应产生错误。
- NO_UNSIGNED_SUBTRACTION:
- 当从无符号整数中减去较大的无符号整数时，不进行溢出处理。
【3】修改严格模式
- 请注意，启用严格模式可以帮助捕获一些潜在的数据问题，但也可能导致某些旧的应用程序或脚本出现问题。在启用严格模式之前，请确保了解其可能的影响，并测试应用程序的兼容性。
- 一般建议在修改前，将原有的严格模式进行备份，防止无法恢复至未改动之前的状态
【3.1】临时修改
-- 仅修改当前会话的严格模式
set session sql_mode = '';
-- 退出当前会话后，自动恢复
【3.2】永久修改
-- 修改全局的严格模式
set global sql_mode = '';
-- 退出当前会话后，依旧保留修改
- 通过
my.ini或my.conf配置文件进行设置
- 如果不进行严格模式的配置，MySQL将默认设置部分的严格模式
- 本人就没有在配置文件中配置严格模式，所有严格模式有很多
[mysqld]
...
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
