本文共 2812 字，大约阅读时间需要 9 分钟。

MySQL 数据类型全面解析

MySQL 中的数据类型是数据库设计中至关重要的一部分。不同的数据类型在存储数据时有不同的特点和限制，选择合适的数据类型可以显著地提高数据库的效率和稳定性。本文将从多个维度全面解析 MySQL 中常见的数据类型。

一、数据类型的属性

在创建数据库或表时，可以通过指定字符集来影响数据的存储和处理。以下是几种常见的字符集设置方式：

CREATE DATABASE IF NOT EXISTS dbtest12 CHARACTER SET 'utf8';SHOW CREATE DATABASE dbtest12;

CREATE TABLE temp(id INT) CHARACTER SET 'utf8';SHOW CREATE TABLE temp;

CREATE TABLE temp1(id INT, `name` VARCHAR(15) CHARACTER SET 'gbk');SHOW CREATE TABLE temp1;

二、整数类型

MySQL 中的整数类型分为五种：TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）和BIGINT。这些类型根据存储空间和范围的不同，适用于不同的场景。

(一) 可选属性

(二) 适用场景

• INT：适用于一般的整数值存储，显示宽度默认为11。
• BIGINT：适用于较大的整数值，显示宽度默认为19。

三、浮点类型

浮点类型用于存储带有小数的数值，MySQL支持单精度和双精度浮点数类型。DECIMAL类型则用于高精度小数的存储。

1. 数据精度说明

• 单精度浮点数：FLOAT，默认显示宽度为float(6)。
• 双精度浮点数：DOUBLE，默认显示宽度为double(15)。

2. 定点数类型

DECIMAL(M,D)类型用于存储高精度小数，M为精度，D为标度。其最大取值范围与DOUBLE类型一致，但有效数据范围由M和D决定。

四、位类型

BIT类型用于存储二进制值，类似于010110。默认显示宽度为1位，最大支持64位。

示例

CREATE TABLE test_bit1(f1 BIT, f2 BIT(5), f3 BIT(64));INSERT INTO test_bit1(f1) VALUES(1);INSERT INTO test_bit1(f2) VALUES(23);

读取方法

• 使用BIN()或HEX()函数可以将二进制字段转换为十进制或十六进制表示。

五、日期与时间类型

MySQL 提供多种日期和时间类型，根据存储空间和时间范围的不同，适用于不同的场景。

1. 年份类型 (YEAR)

• 存储空间：1字节。
• 示例：YEAR(4)表示4位年份，YEAR(2)表示两位年份（如99表示1999，00表示2000）。

2. 日期类型 (DATE)

• 存储空间：3字节。
• 示例：DATE('2020-10-01')。

3. 时间类型 (TIME)

• 存储空间：3字节。
• 示例：TIME('12:35:29')。

4. DATETIME 类型

• 存储空间：8字节。
• 表示完整的日期和时间，格式为YYYY-MM-DD HH:MM:SS。

5. TIMESTAMP 类型

• 存储空间：4字节。
• 时间范围较小（1970-01-01到2038-01-19），并与时区相关。

TIMESTAMP 和 DATETIME 的区别

• 存储空间：TIMESTAMP更小。
• 时间范围：TIMESTAMP更小。
• 时区：TIMESTAMP与时区相关。

六、文本字符串类型

文本字符串类型主要包括CHAR、VARCHAR、TEXT等，用于存储短文本和长文本。

1. CHAR 与 VARCHAR

• CHAR：固定长度字符串，默认长度为1。
• VARCHAR：可变长度字符串，必须指定长度，且默认长度为255。

2. TEXT 类型

• 用于存储较长的文本，MySQL不允许 TEXT 类型作为主键。

七、枚举类型 (ENUM)

ENUM类型用于存储可选值，定义时需指定成员的取值范围。

示例

CREATE TABLE test_enum(  season ENUM('春', '夏', '秋', '冬', 'unknow'));INSERT INTO test_enum VALUES('春'), ('秋');

八、集合类型 (SET)

SET类型用于存储字符集合，支持一次选择多个成员。

示例

CREATE TABLE test_set(s SET ('A', 'B', 'C'));INSERT INTO test_set VALUES('A'), ('A,B');

九、二进制字符串类型

1. BINARY 与 VARBINARY

• BINARY：固定长度二进制字符串，默认长度为1。
• VARBINARY：可变长度二进制字符串，必须指定长度。

2. BLOB 类型

• 用于存储二进制大对象，支持TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。

十、JSON 类型

JSON类型用于存储和验证结构化数据。

示例

CREATE TABLE test_json(js JSON);INSERT INTO test_json VALUES('{"name":"songhk", "age":18, "address":{"province":"beijing","city":"beijing"}}');

查询示例

SELECT js -> '$.name' AS NAME, js -> '$.age' AS age , js -> '$.address.province' AS province, js -> '$.address.city' AS city FROM test_json;

十一、空间类型

MySQL 支持空间类型用于存储地理数据，包括GEOMETRY、POINT、LINESTRING等。

示例

CREATE TABLE test_space(  geom GEOMETRY,  point POINT,  polygon POLYGON);

通过以上内容，可以更好地理解和选择合适的 MySQL 数据类型，提升数据库的性能和稳定性。