MySQL基本数据类型

合集 - MySQL(5)
1.MySQL介绍
2.MySQL基本数据类型
3.MySQL 多表查询 - 缺练习解析4.MySQL基本语法 约束条件 查询条件5.MySQL进阶语法【视图 触发器 事务 存储过程 流程控制】
收起

基本数据类型

【一】整形

  • 整形默认带符号，如果希望使用无符号，可以设置约束条件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
);

image-20240131185221874

【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

image-20240131190211914

image-20240131190528847

【2】SMALLINT

insert t2 values(-32768,0);
insert t2 values(32767,65535);

image-20240131191158761

【3】MEDIUMINT

insert t3 values(-8388608,0);
insert t3 values(8388607,16777215);

image-20240131191500528

【4】INT

insert t4 values(-2147483648,0);
insert t4 values(2147483647,4294967295);

image-20240131191610558

【5】BIGINT

insert t5 values(-9223372036854774808,0);
insert t5 values(9223372036854774807,18446744073709551615);

image-20240131191701670

【6】整形数据的指定宽度

  • 当你指定整数类型的字段宽度时，它会影响显示的宽度，但不会改变数据的存储方式或范围。
# 通过约束条件zerofill来展示
create table t6(int_width tinyint(3) unsigned zerofill);

-- 插入数据
insert t6 values(255);
insert t6 values(24);
insert t6 values(2);

image-20240131195541894

  • 当指定宽度后，哪怕超出整数类型【TINYINT】的最大取值范围后，并不会报错，但是会被变成最大值
  • 注：目前测下来只有tinyint是不会报错，其他整数类型均会报错

image-20240131195754325

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

image-20240131200443475

image-20240131201002462

【二】浮点型

数据类型 字节数 取值范围
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
    -- 插入同样的值
);

image-20240131202038871

-- 最大值
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
    -- 插入同样的值
);

image-20240131202859288

  • 一定注意，浮点数的指定宽度的第一个参数是总位数
    • 如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;

image-20240131204714626

  • 通过char_length关键字获得字符长度
-- 通过char_length关键字得到字符长度
select char_length(str1) from t1;
select char_length(str2) from t1;

image-20240131205102712

  • 需要注意，指定的宽度是字符数而不是字节数

image-20240131212212608

-- 遇到的问题
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：年月日
    • 'YYYY-MM-DD'
    • 2000-1-1
  • datetime：年月日 时分秒
    • 'YYYY-MM-DD HH:MM:SS'
    • 2000-1-2 11:11:11
  • timestamp：时间戳
    • 与 DATETIME 类似，但在插入或更新时会自动转换为当前时区。
  • time：时分秒
    • 'HH:MM:SS'
    • 11:11:11
  • year：年
    • 'YYYY'
    • 2000
-- 建表
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)
;

image-20240131210141851

【五】枚举

【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
# 只能传一个值

image-20240131212800074

【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'); -- 重复填写不会报错，但并不会保存重复值

image-20240131213447198

image-20240131213634321

【六】严格模式

【1】查看严格模式

-- 查看全局严格模式
SELECT @@GLOBAL.sql_mode;
-- 查看当前会话的严格模式
SELECT @@SESSION.sql_mode;

-- 显示所有变量名中包含 "mode" 的系统变量及其对应的值
-- 会有sql_mode
show variables like "%mode";

image-20240131193851886

【2】常见的严格模式介绍

  1. STRICT_TRANS_TABLES:
    • 当插入或更新数据时，对事务安全表（支持事务的表）进行更严格的数据验证。
  2. STRICT_ALL_TABLES:
    • 对所有表执行严格模式检查，包括非事务安全的表。
  3. NO_ZERO_DATE:
    • 不允许日期字段存储 '0000-00-00'。
  4. NO_ZERO_IN_DATE:
    • 不允许日期字段的零值。
  5. ERROR_FOR_DIVISION_BY_ZERO:
    • 对于除以零的操作，产生错误而不是警告。
  6. NO_AUTO_CREATE_USER:
    • 不允许 GRANT 语句自动创建用户。
  7. ONLY_FULL_GROUP_BY:
    • 要求 GROUP BY 子句中的列必须是 SELECT 列的子集，而不是任意表达式。
  8. NO_ENGINE_SUBSTITUTION:
    • 如果指定的存储引擎不可用，不要尝试使用其他存储引擎，而应产生错误。
  9. NO_UNSIGNED_SUBTRACTION:
    • 当从无符号整数中减去较大的无符号整数时，不进行溢出处理。

【3】修改严格模式

  • 请注意，启用严格模式可以帮助捕获一些潜在的数据问题，但也可能导致某些旧的应用程序或脚本出现问题。在启用严格模式之前，请确保了解其可能的影响，并测试应用程序的兼容性。
  • 一般建议在修改前，将原有的严格模式进行备份，防止无法恢复至未改动之前的状态

【3.1】临时修改

-- 仅修改当前会话的严格模式
set session sql_mode = '';
-- 退出当前会话后，自动恢复

image-20240131194245024

【3.2】永久修改

-- 修改全局的严格模式
set global sql_mode = '';
-- 退出当前会话后，依旧保留修改

image-20240131194532571

  • 通过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"
posted @   Lea4ning  阅读(18)  评论(0)  编辑  收藏  举报
相关博文：
· MySQL基本语法 约束条件 查询条件
· MySQL介绍
· MySQL-12.MySQL数据类型
· MySQL数据类型(精)
· MySQL 快速入门（一）
阅读排行：
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
点击右上角即可分享
微信分享提示