MySQL数据中的数据类型

MySQL数据中的数据类型

数据类型(data_type) 是指系统中所允许的数据的类型。MySQL数据类型定义了列中可以存储什么数据以及存储规则

数据库中的每个列都应该有适当的整数类型，用于限制或允许该列中存储的数据。列如，列中存储的为数字，则相应的数据类型应该为数值类型

如果使用错误的数据类型可能会严重影响应用程序的功能和性能，所以在设计表时，应该特别重视数据列所用的数据类型。更改包含数据的列不是一件小事，这样做可能会导致数据丢失。因此，在创建表时必须为每个列设置正确的数据类型和长度。

MySQL的数据类型大概有5种，分别是整数类型、浮点数类型和定点数类型、日期和时间类型、字符串类型、二进制类型等。

1. MySQL整数类型

整数类型又称数值类型数据，数值型数据类型主要用来存储数字。

MySQL 提供了多种数值类型数据，不同的数据类型提供不同的取值范围，可以存储的值范围越大，所需的存储空间也越大。

MySQL 主要提供的整数类型有 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，其属性字段可以添加 AUTO_INCREMENT 自增约束条件。

类型名称	说明	存储需求
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

• 案例

用utf8mb4创建test1库
mysql> create database test1 charset utf8mb4;
使用test1库；
mysql> use test1;
在test1库下创建t1表，id列用int型，name列用varchar型，age用tinyint型
mysql> create table t1(id int ,name varchar(64) ,age tinyint);

说明：手机号是无法存储到int的。一般是使用char类型来存储手机

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。

类型名称	说明	存储需求
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 个字节。

mysql> CREATE TABLE test2 (
    -> name VARCHAR(255)
    -> );
Query OK, 0 rows affected (0.00 sec)

4. MySQL 日期和时间类型

MySQL 中有多处表示日期的数据类型: YEAR、TIME、DATE、DATETIME、TIMESTAMP。根据不同需求选择不同数据类型

每一个类型都有合法的取值范围，当指定确定不合法的值时，系统将“零”值插入数据库中。

下表中列出了 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 database timedb charset utf8;
Query OK, 1 row affected (0.00 sec)

mysql> create table t1( id int, name varchar(255), date1 date, date2 time, date3 daatetime, date4 timestamp, date5 year);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (1,'UPythonFish','2022-1-8','11:11:11','2022-1-8','2022-1-8','2022');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------+-------------+------------+----------+---------------------+---------------------+-------+
| id   | name        | date1      | date2    | date3               | date4               | date5 |
+------+-------------+------------+----------+---------------------+---------------------+-------+
|    1 | UPythonFish | 2022-01-08 | 11:11:11 | 2022-01-08 00:00:00 | 2022-01-08 00:00:00 |  2022 |
+------+-------------+------------+----------+---------------------+---------------------+-------+
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’。
  

5. MySQL 二进制类型

MySQL 中的二进制字符串有BIT、BINARY、VARBINARY、TINYBLOB、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='192.168.15.61', user='root', passwd='MySQL@666', db='db01', 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("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("1.jpg", "wb")
        f.write(d)
        f.close()


# 下面一句的作用是：运行本程序文件时执行什么操作
if __name__ == "__main__":

    test = BlobDataTestor()

    try:
        test.setup()
        test.testRWBlobData()
        # test.teardown()
    finally:
        test.closedb()

6. MySQL 系统变量

在MySQL 数据库，变量分为系统变量和用户自定义变量。系统变量以 @@ 开头，用户自定义变量以 @ 开头。

服务器维护两种系统变量，即全局变量(GLOBAL VARIABLES) 和会话变量(SESSION VARIABLES)。全局变量影响 MySQL 服务的整体运行方式，会话变量影响具体客户端连接的操作。

每一个客户端成功连接服务器后，都会产生与之对应的会话。会话期间，MySQL 服务实例会在服务器内存中生成与该会话对应的会话变量，这些会话变量的初始值是全局变量值得拷贝。

6.1 查看系统变量

• 查看全局变量信息

SHOW GLOBAL VARIABLES; 

• 查看与当前会话相关的所有会话变量以及全局变量（其中，SESSION 关键字可以省略。）

SHOW GLOBAL VARIABLES;

上一篇>>>操作数据库的神器，SQL语言 下一篇>>>MySQL数据表