MySQL之基本数据类型

合集 - MySQL数据库炒年糕(16)
1.MySQL之初识数据库2.MySQL之MySQL数据库安装3.MySQL之SQL语句+严格模式4.MySQL之存储引擎
5.MySQL之基本数据类型
6.MySQL之约束条件7.MySQL之外键介绍8.MySQL之修改表和复制表9.MySQL之筛选和过滤条件10.MySQL之多表操作11.MySQL之pymysql模块12.MySQL之视图、触发器、存储过程、函数和流程控制13.MySQL之事务14.MySQL之索引15.MySQL补充：数据库的三大范式16.补充 windows系统安装多个MySQL版本
收起

数据类型：

类型 类型举例
整数类型 TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT
浮点类型 FLOAT、DOUBLE
定点数类型 DECIMAL
位类型 BIT
日期时间类型 YEAR、TIME、DATE、DATETIME、TIMESTAMP
文本字符串类型 CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
枚举类型 ENUM
集合类型 SET
二进制字符串类型 BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON类型 JSON对象、JSON数组
空间数据类型 单值类型：GEOMETRY、POINT、LINESTRING、POLYGON；
集合类型：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、 GEOMETRYCOLLECTION

常见数据类型的属性：

关键字 含义
NULL 数据可包含NULL值
NOT NULL 数据列不允许包含NULL值
DEFAULT 默认值
PRIMARY KEY 主键
AUTO_INCREMENT 自动递增，适用于整数类型
UNISIGNED 无符号
CHARACTER SET name 指定一个字符集

创建表的完整语法

-- 语法
create table 表名(
	字段名1 类型（宽度） 约束条件，
    字段名2 类型（宽度） 约束条件，
    字段名3 类型（宽度） 约束条件
)
 
-- 注意
1. 在同一张表中字段名不能重复
2. 宽度和约束条件是可选的（可写可不写）而字段名和字段类型是必须的
	约束条件写的话 也支持写多个
	字段名1 类型（宽度） 约束条件1 约束条件2....，
	create table t5(id); # 报错 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
3. 最后一行不能有逗号
	create table t6(
    	id int,
        name char,
    ); # 报错 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 4

宽度

宽度：一般情况下指的是对存储数据的限制

M : 表示显示宽度，M的取值范围是(0, 255)。例如，int(5)：当数据宽度小于5位的时候在数字前面需要用字符填满宽度。该项功能需要配合“ ZEROFILL ”使用，表示用“0”填满宽度，否则指定显示宽度无效。

create table t7(name char);  默认宽度是1
desc t7;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| name  | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
 
insert into t7 values('xiao');
针对不同的版本会出现不同的效果
	（1）5.6版本默认没有开启严格模式，规定只能存一个字符，你给了多个字符，那么我将会自动帮你截取
	（2）5.7版本及以上版本默认自动开启了严格模式，那么固定只能存几个，就不能超出字符宽度，一旦超出范围，就会立刻报错 ERROR 1406 (22001): Data too long for column 'name' at row 1
	
# 使用数据库的准则：
能尽量少的让数据库干活就尽量少，不要给数据库增加额外的压力

约束条件 null,not null

not null 表示不能插入null
默认是可以插入null的，只能通过约束条件来实现不能插入空
create table t8(id int,name char not null); 
desc t8;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| name  | char(1) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
 
insert t8 values(1,null);
#  Column 'name' cannot be null

了解

宽度和约束条件到底是什么关系

  • 宽度是用来限制数据的存储
  • 约束条件是在宽度的基础之上增加的额外的约束

整型

  • 分类

    TINYINT、SAMLLINT、MEDUIMINT、INT、 BINGINT

  • 作用

​ 存储年龄、ID、等级、号码等

整数类型 字节 无符号数的取值范围 有符号数的取值范围
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 
-- 以TINYINT为例
1、是否有符号？
    默认情况下是带符号的
2、超出最大范围会如何？
    超出限制只能存最大接受值
create table t9(id tinyint);
insert into t9 values(-129),(256); # 报错 Out of range value for column 'id' at row 1
 
insert into t9 values(-128),(127);
select * from t9;
+------+
| id   |
+------+
| -128 |
|  127 |
+------+
# 默认带符号

约束条件之unsigned 无符号

create table t10(id tinyint unsigned);
 
create table t11(id int);
# int默认也是带符号的
# 整型默认情况下都是带有符号的
 
## 针对整型 括号里的宽度到底是干嘛的
create table t12(id int(8));
desc t12;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id    | int(8) | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
 
insert into t12 values(123456789)
select * from t12;
+-----------+
| id        |
+-----------+
| 123456789 |
+-----------+
限制了 8位 却存储了 9 位
 
-- 特例
只有整型括号里面的数组不是表示限制位数而是显示长度
id int(8)
	如果数字没有超出8位，那么默认用空格填充至8位
	如果数字超出了8位，那么有几位就存几位（但还是要遵守最大范围）

约束条件之 zerofill

create table t13(id int(8) unsigned zerofill); # 用0填充至8位
desc t13;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type                     | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| id    | int(8) unsigned zerofill | YES  |     | NULL    |       |
+-------+--------------------------+------+-----+---------+-------+
insert into t13 values(1);
insert into t13 values(123456789);
select * from t13;
+-----------+
| id        |
+-----------+
|  00000001 |  # 数字不够 8 位 用 0 填充
| 123456789 |
+-----------+

总结

  • 针对整型字段，括号内无需指定宽度，因为它默认的宽度已经足够显示所有数据了

浮点型

  • 分类

​ FLOAT、DOUBLE、DECIMAL

  • 作用

​ 身高、体重、薪资

数据类型 字节数 取值范围
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

存储限制

float(255,30) # 总共255位，小数部分占30位
double(255,30) # 总共255位，小数部分占30位
decimal(65,30) # 总共65位，小数部分占30位
只要是浮点型，括号后面有两个数字，第一个数字表示的总位数，第二个数字表示的小数位

精确度验证

create table t15(id float(255,30));
create table t16(id double(255,30));
create table t17(id decimal(65,30));
 
insert into t15 values(1.111111111111111111111111111111);
insert into t16 values(1.111111111111111111111111111111);
insert into t17 values(1.111111111111111111111111111111);
 
精确度比较
float < double < decimal
# 要结合实际场景使用，三者都可使用

image

开发中经验：
由于 DECIMAL 数据类型的精准性，在我们的项目中，除了极少数（比如商品编号）用到整数类型外，其他的数值都用的是 DECIMAL，原因就是这个项目所处的零售行业，要求精准，一分钱也不能差。

思考题

问题1：FLOAT 和 DOUBLE 这两种数据类型的区别是啥呢？
FLOAT 占用字节数少，取值范围小；DOUBLE 占用字节数多，取值范围也大。

问题2：为什么浮点数类型的无符号数取值范围，只相当于有符号数取值范围的一半，也就是只相当于 有符号数取值范围大于等于零的部分呢？
MySQL 存储浮点数的格式为： 符号(S) 、 尾数(M) 和 阶码(E) 。因此，无论有没有符号，MySQL 的浮 点数都会存储表示符号的部分。因此， 所谓的无符号数取值范围，其实就是有符号数取值范围大于等于零的部分。

字符型

字符串类型 特点 长度 长度范围 占用的存储空间
CHAR(M) 固定长度 M 0 <= M <= 255 M 个字节
VARCHAR(M) 可变长度 M 0 <= M <= 65535 (实际长度 + 1) 个字节

CHAR 类型：

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

VARCHAR(M) 定义时， 必须指定 长度M，否则报错。
MySQL4.0版本以下，varchar(20)：指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字 3字节） ；MySQL5.0版本以上，varchar(20)：指的是20字符。
检索VARCHAR类型的字段数据时，会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空 间为字符串实际长度加1个字节。

  • 分类
char
	定长
	char(4) 数据超过四个字符直接报错，不够四个字符空格补全
varchar
	变长
	varchar(4) 数据超过四个字符直接报错，不够有几个存几个
 
create table t18(name char(4));
create table t19(name varchar(4));
 
insert into t18 values('b');
insert into t19 values('b');
 
select * from t18;
+------+
| name |
+------+
| b    |
+------+
 
select * from t19;
+------+
| name |
+------+
| b    |
+------+
两者一模一样，看不出区别
 
# 介绍一个小方法 char_length 统计字段长度
select char_length(name) from t18;
+-------------------+
| char_length(name) |
+-------------------+
|                 1 |
+-------------------+
select char_length(name) from t19;
+-------------------+
| char_length(name) |
+-------------------+
|                 1 |
+-------------------+
还是看不出区别
 
"""
首先可以肯定的是 char硬盘上存的绝对是真正的数据，带有空格的
但是在显示的时候MySQL会自动将多余的空格剔除
"""
# 再次修改sql——mode，让MySQL不要做自动剔除操作
set global sql_mode = 'STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';
 
select char_length(name) from t18;
+-------------------+
| char_length(name) |
+-------------------+
|                 4 |
+-------------------+
select char_length(name) from t19;
+-------------------+
| char_length(name) |
+-------------------+
|                 1 |
+-------------------+
现在可以看出区别了

char 和 varchar对比

char
	缺点:浪费空间
	优点:存取都很简单，直接按照固定的字符存取数据即可
	例如：xiao quan zheng
	存按照五个字符存，取也直接按照五个字符取
varchar
	优点:节省空间
	缺点:存取较为麻烦
	例如：xiao quan zheng
	1bytes+xiao 1bytes+quan 1bytes+zheng
	存的时候需要制作报头，取的时候也需要先读取报头，之后才能读取真实数据
	
以前基本上都是用的char，其实现在用的varchar的也挺多，建议使用varchar

时间类型

  • 分类

​ date：年月日 2000-1-1

​ datetime：年月日时分秒 2000-1-1 11:11:11

​ time：时分秒 11:11:11

​ Year：2000(不能写的太早，不然会报错)

create table student(
	id int,
    name varchar(16),
    born_year year,
    birth date,
    study_time time,
    reg_time datetime
);
desc student;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | YES  |     | NULL    |       |
| name       | varchar(16) | YES  |     | NULL    |       |
| born_year  | year(4)     | YES  |     | NULL    |       |
| birth      | date        | YES  |     | NULL    |       |
| study_time | time        | YES  |     | NULL    |       |
| reg_time   | datetime    | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
insert into student values(1,'xiao','2000','2000-1-1','11:11:11','2000-1-1 11:11:11');
 
select * from student;
+------+------+-----------+------------+------------+---------------------+
| id   | name | born_year | birth      | study_time | reg_time            |
+------+------+-----------+------------+------------+---------------------+
|    1 | xiao |      2000 | 2000-01-01 | 11:11:11   | 2000-01-01 11:11:11 |
+------+------+-----------+------------+------------+---------------------+

枚举与集合类型

  • 分类
枚举(enum) 多选一 例如性别
集合(set) 多选多 例如爱好
  • 具体使用
create table user(
	id int,
    name char(16),
    gender enum('male','female','others')
);
desc user;
+--------+--------------------------------+------+-----+---------+-------+
| Field  | Type                           | Null | Key | Default | Extra |
+--------+--------------------------------+------+-----+---------+-------+
| id     | int(11)                        | YES  |     | NULL    |       |
| name   | char(16)                       | YES  |     | NULL    |       |
| gender | enum('male','female','others') | YES  |     | NULL    |       |
+--------+--------------------------------+------+-----+---------+-------+
 
insert into user values(1,'xiao','male');
select * from user;
+------+------------------+--------+
| id   | name             | gender |
+------+------------------+--------+
|    1 | xiao             | male   |
+------+------------------+--------+
 
insert into user values(2,'quan','xxxxoooo');
# ERROR 1265 (01000): Data truncated for column 'gender' at row 1
# 枚举字段后期在存数据的时候只能从枚举里面选择一个存储
 
create table teacher(
	id int,
    name char(16),
    gender enum('male','female','others'),
    hobby set('read','drink','eat','play games')
);
desc teacher;
+--------+----------------------------------------+------+-----+---------+-------+
| Field  | Type                                   | Null | Key | Default | Extra |
+--------+----------------------------------------+------+-----+---------+-------+
| id     | int(11)                                | YES  |     | NULL    |       |
| name   | char(16)                               | YES  |     | NULL    |       |
| gender | enum('male','female','others')         | YES  |     | NULL    |       |
| hobby  | set('read','drink','eat','play games') | YES  |     | NULL    |       |
+--------+----------------------------------------+------+-----+---------+-------+
insert into teacher values(1,'xiao','male','read');
insert into teacher values(1,'quan','female','drink,eat,play games');
insert into teacher values(1,'zheng','others','ob');
# ERROR 1265 (01000): Data truncated for column 'hobby' at row 1
 
select * from teacher;
+------+------------------+--------+----------------------+
| id   | name             | gender | hobby                |
+------+------------------+--------+----------------------+
|    1 | xiao             | male   | read                 |
|    1 | quan             | female | drink,eat,play games |
+------+------------------+--------+----------------------+
前两个正常，第三个报错
结论：集合可以只写一个，也可以写多个，但是不能写集合里没有的。
posted @   Xiao0101  阅读(47)  评论(0)  编辑  收藏  举报
相关博文：
· python基础之数据类型
· python中级之数据类型内置方法
· MySQL------数据类型重点关注
· 【MySQL】03_数据类型
· MySQL数据类型
阅读排行：
· 终于写完轮子一部分：tcp代理 了，记录一下
· 震惊！C++程序真的从main开始吗？99%的程序员都答错了
· 别再用vector＜bool＞了！Google高级工程师：这可能是STL最大的设计失误
· 单元测试从入门到精通
· 【硬核科普】Trae如何「偷看」你的代码？零基础破解AI编程运行原理
点击右上角即可分享
微信分享提示

目录