MySQL 8 教程

4.1 数据类型介绍

MySQL支持多种数据类型，主要分为以下几大类：

• 整数类型
• 浮点数类型和定点数类型
• 日期与时间类型
• 文本字符串类型
• 二进制字符串类型

4.1.1 整数类型

MySQL提供了多种整数类型，它们的区别在于取值范围和存储空间不同。选择合适的整数类型可以节省存储空间并提高性能。

-- 创建包含各种整数类型的表
CREATE TABLE integer_types (
    id INT PRIMARY KEY AUTO_INCREMENT,
    tiny_col TINYINT,
    small_col SMALLINT,
    medium_col MEDIUMINT,
    int_col INT,
    big_col BIGINT,
    unsigned_tiny TINYINT UNSIGNED,
    unsigned_int INT UNSIGNED
);

-- 插入数据
INSERT INTO integer_types (
    tiny_col, small_col, medium_col, int_col, big_col,
    unsigned_tiny, unsigned_int
) VALUES (
    127, 32767, 8388607, 2147483647, 9223372036854775807,
    255, 4294967295
);

4.1.2 浮点数类型和定点数类型

浮点数类型用于存储小数，包括FLOAT、DOUBLE和REAL。定点数类型用于存储精确的小数，主要是DECIMAL类型。

-- 创建包含浮点数和定点数类型的表
CREATE TABLE numeric_types (
    id INT PRIMARY KEY AUTO_INCREMENT,
    float_col FLOAT,
    double_col DOUBLE,
    decimal_col DECIMAL(10,2),
    decimal_col2 DECIMAL(18,6)
);

-- 插入数据
INSERT INTO numeric_types (
    float_col, double_col, decimal_col, decimal_col2
) VALUES (
    3.1415926, 3.141592653589793, 1234567.89, 123456.789012
);

-- 浮点数的精度问题
-- 注意：以下两个值在浮点数中可能被视为相等
INSERT INTO numeric_types (float_col) VALUES (0.1);
INSERT INTO numeric_types (float_col) VALUES (0.10000000149011612);

4.1.3 日期与时间类型

MySQL提供了多种日期与时间类型，用于存储日期、时间或日期时间组合。

-- 创建包含日期与时间类型的表
CREATE TABLE datetime_types (
    id INT PRIMARY KEY AUTO_INCREMENT,
    date_col DATE,
    time_col TIME,
    datetime_col DATETIME,
    timestamp_col TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    year_col YEAR
);

-- 插入数据
INSERT INTO datetime_types (
    date_col, time_col, datetime_col, year_col
) VALUES (
    '2023-05-15', '14:30:45', '2023-05-15 14:30:45', 2023
);

-- 使用系统日期时间
INSERT INTO datetime_types (
    date_col, datetime_col
) VALUES (
    CURDATE(), NOW()
);

-- 更新记录，timestamp_col会自动更新
UPDATE datetime_types SET date_col = '2023-05-16' WHERE id = 1;

4.1.4 文本字符串类型

文本字符串类型用于存储字符数据，包括CHAR、VARCHAR、TEXT等。

-- 创建包含文本字符串类型的表
CREATE TABLE text_types (
    id INT PRIMARY KEY AUTO_INCREMENT,
    char_col CHAR(10),
    varchar_col VARCHAR(100),
    text_col TEXT,
    mediumtext_col MEDIUMTEXT,
    longtext_col LONGTEXT
);

-- 插入数据
INSERT INTO text_types (
    char_col, varchar_col, text_col, mediumtext_col, longtext_col
) VALUES (
    '固定长度', '这是一个可变长度的字符串', '这是一个文本数据...',
    '这是一个中等大小的文本数据...', '这是一个大型文本数据...'
);

4.1.5 二进制字符串类型

二进制字符串类型用于存储二进制数据，包括BINARY、VARBINARY、BLOB等。

-- 创建包含二进制字符串类型的表
CREATE TABLE binary_types (
    id INT PRIMARY KEY AUTO_INCREMENT,
    binary_col BINARY(10),
    varbinary_col VARBINARY(100),
    blob_col BLOB,
    mediumblob_col MEDIUMBLOB,
    longblob_col LONGBLOB
);

-- 插入二进制数据（通常通过应用程序插入，这里仅作示例）
INSERT INTO binary_types (varbinary_col) VALUES (UNHEX('48656C6C6F20576F726C64'));
-- 上述语句插入的是ASCII字符串"Hello World"的十六进制表示

4.2 如何选择数据类型

选择合适的数据类型对于数据库性能、存储空间和数据完整性至关重要。以下是一些选择数据类型的基本原则：

4.2.1 最小化数据类型的大小

在满足需求的前提下，应尽量选择占用存储空间小的数据类型。这不仅可以节省存储空间，还可以提高数据的读写性能，因为更小的数据类型意味着更少的I/O操作和更快的内存处理。

4.2.2 优先考虑简单数据类型

简单数据类型通常具有更好的性能。例如，整数类型的处理速度比字符类型快，因此，如果可以用整数类型存储数据（如用0和1表示布尔值），应优先选择整数类型。

4.2.3 避免NULL值

NULL值会使索引、索引统计和值比较变得复杂，并且会占用额外的存储空间。在设计表时，应尽量避免使用NULL值，特别是对于索引列。如果必须使用NULL值，应确保该列不会被频繁用于查询条件。

4.2.4 为字符串选择合适的类型

对于字符串类型，应根据数据的长度和特性选择合适的类型：

• 如果数据长度固定或变化很小，使用CHAR类型
• 如果数据长度变化较大，使用VARCHAR类型
• 对于非常长的文本数据，使用TEXT类型系列
• 对于二进制数据，使用BLOB类型系列

4.2.5 为日期和时间选择合适的类型

对于日期和时间类型，应根据需求选择合适的类型：

• 如果只需要日期，使用DATE类型
• 如果只需要时间，使用TIME类型
• 如果需要日期和时间，并且不需要时区支持，使用DATETIME类型
• 如果需要日期和时间，并且需要时区支持，使用TIMESTAMP类型
• 如果只需要年份，使用YEAR类型

4.2.6 为小数选择合适的类型

对于小数类型，应根据精度要求选择合适的类型：

• 如果需要精确计算，特别是对于财务数据，使用DECIMAL类型
• 如果可以接受一定的精度损失，并且需要较高的计算性能，使用FLOAT或DOUBLE类型

-- 不合理的表设计
CREATE TABLE bad_design (
    id INT PRIMARY KEY,
    name VARCHAR(255),  -- 对于大多数姓名来说太长
    age INT,            -- 对于年龄来说太大
    salary FLOAT,       -- 不适合存储财务数据
    hire_date VARCHAR(20)  -- 不适合存储日期
);

-- 合理的表设计
CREATE TABLE good_design (
    id INT PRIMARY KEY,
    name VARCHAR(50),   -- 更合适的长度
    age TINYINT UNSIGNED,  -- 对于年龄来说足够，且使用无符号
    salary DECIMAL(10,2),  -- 适合存储财务数据
    hire_date DATE      -- 使用日期类型
);

4.3 常见运算符介绍

运算符是SQL中用于执行操作的特殊符号。MySQL支持多种类型的运算符，包括算术运算符、比较运算符、逻辑运算符、位运算符等。

4.3.1 运算符概述

MySQL支持的运算符主要分为以下几类：

• 算术运算符
• 比较运算符
• 逻辑运算符
• 位运算符

4.3.2 算术运算符

算术运算符用于执行数学运算，如加减乘除等。

-- 基本算术运算
SELECT 10 + 5 AS addition,
       10 - 5 AS subtraction,
       10 * 5 AS multiplication,
       10 / 5 AS division,
       10 % 3 AS modulo,
       10 DIV 3 AS integer_division;

-- 对表中的数据进行算术运算
SELECT id, name, salary, salary * 1.1 AS increased_salary
FROM employees;

-- 计算两个日期之间的天数差
SELECT DATEDIFF('2023-12-31', '2023-01-01') AS days_diff;

-- 计算年龄
SELECT name, DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), birth_date)), '%Y') + 0 AS age
FROM employees;

4.3.3 比较运算符

比较运算符用于比较两个表达式的值，并返回布尔值（TRUE、FALSE或NULL）。

-- 基本比较运算
SELECT 5 = 5 AS equal,
       5 <> 3 AS not_equal,
       5 < 10 AS less_than,
       10 > 5 AS greater_than;

-- 在WHERE子句中使用比较运算符
SELECT id, name, salary
FROM employees
WHERE salary > 5000;

-- 使用BETWEEN运算符
SELECT id, name, age
FROM employees
WHERE age BETWEEN 25 AND 35;

-- 使用IN运算符
SELECT id, name, department
FROM employees
WHERE department IN ('HR', 'Finance', 'IT');

-- 使用LIKE运算符进行模式匹配
SELECT id, name
FROM employees
WHERE name LIKE 'J%';  -- 查找姓名以J开头的员工

-- 使用IS NULL运算符
SELECT id, name, manager_id
FROM employees
WHERE manager_id IS NULL;  -- 查找没有经理的员工

4.3.4 逻辑运算符

逻辑运算符用于组合多个条件，并返回布尔值。

-- 基本逻辑运算
SELECT TRUE AND TRUE AS and_result,
       TRUE OR FALSE AS or_result,
       NOT FALSE AS not_result,
       TRUE XOR FALSE AS xor_result;

-- 在WHERE子句中使用逻辑运算符
SELECT id, name, salary, department
FROM employees
WHERE salary > 5000 AND department = 'IT';

-- 组合多个条件
SELECT id, name, age, salary
FROM employees
WHERE (age < 30 AND salary > 4000) OR (age >= 30 AND salary > 6000);

-- 使用NOT运算符
SELECT id, name, department
FROM employees
WHERE NOT department = 'HR';

-- 使用XOR运算符
SELECT id, name, age, salary
FROM employees
WHERE (age < 30 XOR salary > 5000);

4.3.5 位运算符

位运算符用于对二进制数进行位级操作。

-- 基本位运算
SELECT 5 & 3 AS bit_and,
       5 | 3 AS bit_or,
       5 ^ 3 AS bit_xor,
       ~5 AS bit_not,
       5 << 1 AS left_shift,
       5 >> 1 AS right_shift;

-- 使用位运算符存储权限信息
-- 假设有以下权限：
-- 1 (0001) - 读取权限
-- 2 (0010) - 写入权限
-- 4 (0100) - 更新权限
-- 8 (1000) - 删除权限

-- 创建用户权限表
CREATE TABLE user_permissions (
    user_id INT PRIMARY KEY,
    permissions INT NOT NULL
);

-- 插入具有读取和写入权限的用户
INSERT INTO user_permissions VALUES (1, 1 | 2);  -- 权限值为3 (0011)

-- 插入具有所有权限的用户
INSERT INTO user_permissions VALUES (2, 1 | 2 | 4 | 8);  -- 权限值为15 (1111)

-- 检查用户是否具有读取权限
SELECT user_id, (permissions & 1) > 0 AS has_read_permission
FROM user_permissions;

-- 检查用户是否具有写入和更新权限
SELECT user_id, ((permissions & 2) > 0 AND (permissions & 4) > 0) AS has_write_and_update_permissions
FROM user_permissions;

-- 添加删除权限
UPDATE user_permissions SET permissions = permissions | 8 WHERE user_id = 1;

-- 移除写入权限
UPDATE user_permissions SET permissions = permissions & ~2 WHERE user_id = 2;

4.3.6 运算符的优先级

当一个表达式包含多个运算符时，MySQL会按照运算符的优先级来决定运算顺序。如果不确定运算符的优先级，建议使用括号来明确运算顺序。

以下是MySQL中运算符的优先级从高到低排列：

-- 以下两个表达式的结果不同，因为乘法的优先级高于加法
SELECT 5 + 3 * 2;  -- 结果为 11 (先计算 3*2=6，再计算 5+6=11)
SELECT (5 + 3) * 2;  -- 结果为 16 (先计算 5+3=8，再计算 8*2=16)

-- 逻辑运算符的优先级
SELECT TRUE OR FALSE AND FALSE;  -- 结果为 TRUE (先计算 FALSE AND FALSE=FALSE，再计算 TRUE OR FALSE=TRUE)
SELECT (TRUE OR FALSE) AND FALSE;  -- 结果为 FALSE (先计算 TRUE OR FALSE=TRUE，再计算 TRUE AND FALSE=FALSE)

-- 比较运算符和逻辑运算符的优先级
SELECT 5 > 3 AND 2 < 4;  -- 结果为 TRUE (先计算两个比较运算，再计算逻辑与)
SELECT 5 > (3 AND 2) < 4;  -- 结果取决于具体实现，建议使用括号明确运算顺序