MySQL数据类型

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;