MySQL数据库中的数据类型
数据类型（data_type）是指系统中所允许的数据的类型。MySQL 数据类型定义了列中可以存储什么数据以及该数据怎样存储的规则。
数据库中的每个列都应该有适当的数据类型，用于限制或允许该列中存储的数据。例如，列中存储的为数字，则相应的数据类型应该为数值类型。
如果使用错误的数据类型可能会严重影响应用程序的功能和性能，所以在设计表时，应该特别重视数据列所用的数据类型。更改包含数据的列不是一件小事，这样做可能会导致数据丢失。因此，在创建表时必须为每个列设置正确的数据类型和长度。
MySQL 的数据类型有大概可以分为 5 种，分别是整数类型、浮点数类型和定点数类型、日期和时间类型、字符串类型、二进制类型等。
1.MySQL整数类型
整数类型又称数值型数据，数值型数据类型主要用来存储数字。
MySQL 提供了多种数值型数据类型，不同的数据类型提供不同的取值范围，可以存储的值范围越大，所需的存储空间也会越大。
MySQL 主要提供的整数类型有 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，其属性字段可以添加 AUTO_INCREMENT 自增约束条件。
下表中列出了 MySQL 中的数值类型。
| 类型名称 | 说明 | 存储需求 |
|---|---|---|
| TINYINT | 很小的整数 | 1个字节 |
| SMALLINT | 小的整数 | 2个宇节 |
| MEDIUMINT | 中等大小的整数 | 3个字节 |
| INT (INTEGHR) | 普通大小的整数 | 4个字节 |
| BIGINT | 大整数 | 8个字节 |
从上表中可以看到，不同类型的整数存储所需的字节数不相同，占用字节数最小的是 TINYINT 类型，占用字节最大的是 BIGINT 类型，占用的字节越多的类型所能表示的数值范围越大。
根据占用字节数可以求出每一种数据类型的取值范围。例如，TINYINT 需要 1 个字节（8bit）来存储，那么 TINYINT 无符号数的最大值为 28-1，即 255；TINYINT 有符号数的最大值为 27-1，即 127。其他类型的整数的取值范围计算方法相同，
如下表所示。
| 类型名称 | 说明 | 存储需求 |
|---|---|---|
| TINYINT | -128〜127 | 0 〜255 |
| SMALLINT | -32768〜32767 | 0〜65535 |
| MEDIUMINT | -8388608〜8388607 | 0〜16777215 |
| INT (INTEGER) | -2147483648〜2147483647 | 0〜4294967295 |
| BIGINT | -9223372036854775808〜9223372036854775807 | 0〜18446744073709551615 |
- 测试int数据类型
#用utf8mb4创建world库
mysql> CREATE DATABASE world CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 1 row affected (0.00 sec)
#建表；
mysql> use world;
Database changed
mysql> CREATE TABLE t2(id int);
Query OK, 0 rows affected (0.17 sec)
#查看建表语句
mysql> show create table t2;
+-------+-----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
#查看表结构
mysql> desc t2;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
#插入数据
mysql> insert t2 values('-2147483647');
Query OK, 1 row affected (0.00 sec)
mysql> insert t2 values('123212321');
Query OK, 1 row affected (0.00 sec)
#插入的数据超出int类型范围
mysql> insert t2 values('-2147483649');
ERROR 1264 (22003): Out of range value for column 'id' at row 1
#插入的数据超出int类型长度
mysql> insert t2 values('12321232112');
ERROR 1264 (22003): Out of range value for column 'id' at row 1
2.MySQL小数类型
MySQL 中使用浮点数和定点数来表示小数。
浮点类型有两种，分别是单精度浮点数（FLOAT）和双精度浮点数（DOUBLE）；定点类型只有一种，就是 DECIMAL。
浮点类型和定点类型都可以用(M, D)来表示，其中M称为精度，表示总共的位数；D称为标度，表示小数的位数。
浮点数类型的取值范围为 M（1～255）和 D（1～30，且不能大于 M-2），分别表示显示宽度和小数位数。M 和 D 在 FLOAT 和DOUBLE 中是可选的，FLOAT 和 DOUBLE 类型将被保存为硬件所支持的最大精度。DECIMAL 的默认 D 值为 0、M 值为 10。
下表中列出了 MySQL 中的小数类型和存储需求。
| 类型名称 | 说明 | 存储需求 |
|---|---|---|
| FLOAT | 单精度浮点数 | 4 个字节 |
| DOUBLE | 双精度浮点数 | 8 个字节 |
| DECIMAL (M, D) | 压缩的“严格”定点数 | M+2 个字节 |
DECIMAL 类型不同于 FLOAT 和 DOUBLE。DOUBLE 实际上是以字符串的形式存放的，DECIMAL 可能的最大取值范围与 DOUBLE 相同，但是有效的取值范围由 M 和 D 决定。如果改变 M 而固定 D，则取值范围将随 M 的变大而变大。
从上表中可以看到，DECIMAL 的存储空间并不是固定的，而由精度值 M 决定，占用 M+2 个字节。
FLOAT 类型的取值范围如下：
- 有符号的取值范围：-3.402823466E+38～-1.175494351E-38。
- 无符号的取值范围：0 和 -1.175494351E-38～-3.402823466E+38。
DOUBLE 类型的取值范围如下：
- 有符号的取值范围：-1.7976931348623157E+308～-2.2250738585072014E-308。
- 无符号的取值范围：0 和 -2.2250738585072014E-308～-1.7976931348623157E+308。
3.MySQL字符串类型
字符串类型用来存储字符串数据，还可以存储图片和声音的二进制数据。字符串可以区分或者不区分大小写的串比较，还可以进行正则表达式的匹配查找。
MySQL 中的字符串类型有 CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET 等。
下表中列出了 MySQL 中的字符串数据类型，括号中的M表示可以为其指定长度。
| 类型名称 | 说明 | 存储需求 |
|---|---|---|
| CHAR(M) | 固定长度非二进制字符串 | M 字节，1<=M<=255 |
| VARCHAR(M) | 变长非二进制字符串 | L+1字节，在此，L< = M和 1<=M<=255 |
| TINYTEXT | 非常小的非二进制字符串 | L+1字节，在此，L<2^8 |
| TEXT | 小的非二进制字符串 | L+2字节，在此，L<2^16 |
| MEDIUMTEXT | 中等大小的非二进制字符串 | L+3字节，在此，L<2^24 |
| LONGTEXT | 大的非二进制字符串 | L+4字节，在此，L<2^32 |
| ENUM | 枚举类型，只能有一个枚举字符串值 | 1或2个字节，取决于枚举值的数目 (最大值为65535) |
VARCHAR 和 TEXT 类型是变长类型，其存储需求取决于列值的实际长度（在前面的表格中用 L 表示），而不是取决于类型的最大可能尺寸。
例如，一个 VARCHAR(10) 列能保存一个最大长度为 10 个字符的字符串，实际的存储需要字符串的长度 L 加上一个字节以记录字符串的长度。对于字符 “abcd”，L 是 4，而存储要求 5 个字节。
- 测试char和varchar类型
#建表
mysql> CREATE TABLE t1(id int,name varchar(12));
Query OK, 0 rows affected (0.01 sec)
#插入数据
mysql> INSERT INTO t1 values('1','张三');
Query OK, 1 row affected (0.00 sec)
#字符超过数据类型的限制值
mysql> INSERT INTO t1 values('2','zhangsanlinsi');
ERROR 1406 (22001): Data too long for column 'name' at row 1
- 测试enum类型
#建表
mysql> CREATE TABLE t3(id int,name varchar(10),sex enum('man','girl'));
Query OK, 0 rows affected (0.32 sec)
#插入数据
mysql> INSERT INTO t3 values('1','张三','man');
Query OK, 1 row affected (0.00 sec)
#枚举类型，只能插入指定的值，不能插入额外的值
mysql> INSERT INTO t3 values('1','李四','nan');
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
- 建表测试
#建学生表
mysql> CREATE TABLE student(id int,name varchar(10),sex enum('男','女'),age tinyint,cometime datetime);
Query OK, 0 rows affected (0.01 sec)
#插入数据
mysql> INSERT INTO student values('1','张三','男','19',now());
Query OK, 1 row affected (0.01 sec)
#查看
mysql> select * from student;
+------+--------+------+------+---------------------+
| id | name | sex | age | cometime |
+------+--------+------+------+---------------------+
| 1 | 张三 | 男 | 19 | 2021-09-28 11:13:11 |
+------+--------+------+------+---------------------+
1 row in set (0.00 sec)
4.建表的数据属性
not null： 非空
primary key： 主键（唯一且非空的）
auto_increment： 自增（此列必须是：primary key或者unique key）
unique key： 唯一键（单独的唯一的）
default： 默认值
unsigned： 非负数
comment： 注释
- 案例
#建表
mysql> CREATE TABLE student(
-> id int unsigned primary key auto_increment comment '学生id',
-> name varchar(10) not null comment '学会姓名',
-> sex enum('男','女') default '男' comment '性别',
-> age tinyint unsigned comment '年龄',
-> cometime datetime default now() comment '入学时间',
-> class varchar(12) not null comment '班级',
-> status enum('0','1') default 1 comment '状态');
Query OK, 0 rows affected (0.39 sec)
#查看建表语句
mysql> show create table student;
| student | CREATE TABLE `student` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '学生id',
`name` varchar(10) NOT NULL COMMENT '学会姓名',
`sex` enum('男','女') DEFAULT '男' COMMENT '性别',
`age` tinyint(3) unsigned DEFAULT NULL COMMENT '年龄',
`cometime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间',
`class` varchar(12) NOT NULL COMMENT '班级',
`status` enum('0','1') DEFAULT '0' COMMENT '状态',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
1 row in set (0.00 sec)
#插入数据
mysql> INSERT INTO student(name,class) values('张三','高二3班');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO student(name,class) values('李四','高二3班');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO student(name,class) values('林五','高二3班');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO student(name,sex,age,class) values('杨雪','女','16','高二3班');
Query OK, 1 row affected (0.01 sec)
#查看数据
mysql> select * from student;
+----+--------+------+------+---------------------+------------+--------+
| id | name | sex | age | cometime | class | status |
+----+--------+------+------+---------------------+------------+--------+
| 1 | 张三 | 男 | NULL | 2021-09-28 11:26:42 | 高二3班 | 0 |
| 2 | 李四 | 男 | NULL | 2021-09-28 11:27:33 | 高二3班 | 0 |
| 3 | 林五 | 男 | NULL | 2021-09-28 11:28:11 | 高二3班 | 0 |
| 4 | 杨雪 | 女 | 16 | 2021-09-28 11:32:25 | 高二3班 | 0 |
+----+--------+------+------+---------------------+------------+--------+
4 rows in set (0.00 sec)
5.MySQL日期和时间
MySQL 中有多处表示日期的数据类型：YEAR、TIME、DATE、DTAETIME、TIMESTAMP。当只记录年信息的时候，可以只使用 YEAR 类型。
每一个类型都有合法的取值范围，当指定确定不合法的值时，系统将“零”值插入数据库中。
下表中列出了 MySQL 中的日期与时间类型。
| 类型名称 | 日期格式 | 日期范围 | 存储需求 |
|---|---|---|---|
| YEAR | YYYY | 1901 ~ 2155 | 1 个字节 |
| TIME | HH:MM:SS | -838:59:59 ~ 838:59:59 | 3 个字节 |
| DATE | YYYY-MM-DD | 1000-01-01 ~ 9999-12-3 | 3 个字节 |
| DATETIME | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 | 8 个字节 |
| TIMESTAMP | YYYY-MM-DD HH:MM:SS | 1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC | 4 个字节 |
- 案例
mysql> create table d1(id int,name char, date1 date, date2 time, date3 datetime, date4 timestamp,date5 year);
Query OK, 0 rows affected (0.12 sec)
mysql> insert into d1 values (1, '1', '2021-09-09','12:12:12','2021-09-09','2021-09-09','2021');
Query OK, 1 row affected (0.00 sec)
mysql> select * from d1;
+------+------+------------+----------+---------------------+---------------------+-------+
| id | name | date1 | date2 | date3 | date4 | date5 |
+------+------+------------+----------+---------------------+---------------------+-------+
| 1 | 1 | 2021-09-09 | 12:12:12 | 2021-09-09 00:00:00 | 2021-09-09 00:00:00 | 2021 |
+------+------+------------+----------+---------------------+---------------------+-------+
1 row in set (0.00 sec)
datetime 和 timestamp 之间的区别？
1、两者的存储方式不一样
对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。
对于DATETIME，不做任何改变，基本上是原样输入和输出。
2、两者所能存储的时间范围不一样
timestamp所能存储的时间范围为：‘1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’。
datetime所能存储的时间范围为：‘1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’。
mysql> insert into t1 values (1, '1', '2021-09-09','12:12:12','2221-09-09 12','2221-09-09','2021');
1292 - Incorrect datetime value: '2221-09-09' for column 'date4' at row 1
6.MySQL二进制类型
MySQL 中的二进制字符串有 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。
下表中列出了 MySQL 中的二进制数据类型，括号中的M表示可以为其指定长度。
| 类型名称 | 说明 | 存储需求 |
|---|---|---|
| BIT(M) | 位字段类型 | 大约 (M+7)/8 字节 |
| BINARY(M) | 固定长度二进制字符串 | M 字节 |
| VARBINARY (M) | 可变长度二进制字符串 | M+1 字节 |
| TINYBLOB (M) | 非常小的BLOB | L+1 字节，在此，L<2^8 |
| BLOB (M) | 小 BLOB | L+2 字节，在此，L<2^16 |
| MEDIUMBLOB (M) | 中等大小的BLOB | L+3 字节，在此，L<2^24 |
| LONGBLOB (M) | 非常大的BLOB | L+4 字节，在此，L<2^32 |
- 案例
import pymysql
class BlobDataTestor:
def __init__(self):
self.conn = pymysql.connect(host='127.0.0.1', user='root', passwd='123456', db='db1', port=3306)
def __del__(self):
try:
self.conn.close()
except:
pass
def closedb(self):
self.conn.close()
def setup(self):
cursor = self.conn.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS `Dem_Picture` (
`ID` int(11) NOT NULL auto_increment,
`PicData` mediumblob,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
""")
def testRWBlobData(self):
# 读取源图片数据
f = open("D:\\1.jpg", "rb")
b = f.read()
f.close()
# 将图片数据写入表
cursor = self.conn.cursor()
cursor.execute("INSERT INTO Dem_Picture (PicData) VALUES (%s)", (pymysql.Binary(b)))
# self.conn.commit()
# 读取表内图片数据，并写入硬盘文件
cursor.execute("SELECT PicData FROM Dem_Picture ORDER BY ID DESC limit 1")
d = cursor.fetchone()[0]
cursor.close()
f = open("D:\\1.jpg", "wb")
f.write(d)
f.close()
# 下面一句的作用是：运行本程序文件时执行什么操作
if __name__ == "__main__":
test = BlobDataTestor()
try:
test.setup()
test.testRWBlobData()
# test.teardown()
finally:
test.closedb()
7.MySQL系统变量
在 MySQL 数据库，变量分为系统变量和用户自定义变量。系统变量以 @@ 开头，用户自定义变量以 @ 开头。
服务器维护着两种系统变量，即全局变量（GLOBAL VARIABLES）和会话变量（SESSION VARIABLES）。全局变量影响 MySQL 服务的整体运行方式，会话变量影响具体客户端连接的操作。
每一个客户端成功连接服务器后，都会产生与之对应的会话。会话期间，MySQL 服务实例会在服务器内存中生成与该会话对应的会话变量，这些会话变量的初始值是全局变量值的拷贝。
查看系统变量
#查看MySQL中所有的全局变量信息
mysql> SHOW GLOBAL VARIABLES;
#查看与当前会话相关的所有会话变量以及全局变量。 其中SESSION关键字可以省略
mysql> SHOW SESSION VARIABLES;
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
