数据库Mysql

授课语音

MySQL基础课件

1. 介绍

MySQL是一种流行的关系型数据库，默认端口号是3306。它有以下特点：

免费开源、稳定、功能完善
社区生态活跃、文档丰富
InnoDB存储引擎，支持事务
支持分库分表、读写分离、高可用

字符编码

字符编码是将字符集中的字符与计算机中的二进制转换的方法，是一种映射规则，比如ASCII、gb2312、gbk、gb18030、big5、utf-8、utf-16等。

在实际开发中，正确配置MySQL的字符集非常重要，因为字符集影响到数据的存储和检索，尤其是在多语言和国际化的环境中。建议使用utf8mb4作为默认字符集，排序规则选择utf8mb4_general_ci，因为它是utf-8的超集，支持更多的字符，包括emoji和复杂汉字繁体字。配置包括：

服务器级别配置：找到my.cnf（Linux）或my.ini（Windows）

[mysqld]
# 设置服务器的默认字符集为 utf8mb4
character-set-server=utf8mb4
# 设置服务器的默认排序规则为 utf8mb4_general_ci
collation-server=utf8mb4_general_ci

客户端级别配置：确保和服务器端字符集一致，找到my.cnf或my.ini，修改
```
[client]
# 设置客户端的默认字符集为 utf8mb4
default-character-set=utf8mb4
```

数据库、表、列级别配置

-- 将数据库字符集更改为 utf8mb4
ALTER DATABASE your_database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

-- 将表的字符集更改为 utf8mb4
ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 将列的字符集更改为 utf8mb4，根据特定需求
ALTER TABLE your_table_name MODIFY your_column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

字段类型

数值类型：
- TINYINT（1字节）
- SMALLINT（2字节）
- MEDIUMINT（3字节）
- INT（4字节）
- BIGINT（8字节）
- FLOAT（单精度，4字节）
- DOUBLE（双精度，8字节）
- DECIMAL（存储精确小数，一般存储金额）
字符串类型：
- CHAR（固定长度，最多255字符）
- VARCHAR（可变长度，最多65535字符，实际长度+1/2字节的长度信息）
- TEXT（长文本，最大长度65535字符）
- MEDIUMTEXT（中等长度）
- LONGTEXT（超长文本）
- BINARY（定长二进制，最多255字节）
- VARBINARY（可变长二进制）
- BLOB（二进制大对象）
- MEDIUMBLOB（中等长度）
- LONGBLOB（超长）
日期和时间类型：
- DATETIME（8字节，不涉及时区，格式YYYY-MM-DD HH:MM:SS）
- TIMESTAMP（4字节，包含时区）
- DATE（日期，YYYY-MM-DD）
- TIME（时间）
- YEAR（年份）
布尔类型：实际上是TINYINT(1)，0表示FALSE，1表示TRUE
其他特殊类型：
- ENUM（枚举类型，预定义字符串中选择一个值）
- SET（集合类型，预定义集合中选择0或多个值）

字段类型关键点

UNSIGNED的整数类型（包括TINYINT，SMALLINT，INT和BIGINT）不包含负数，范围会提高一倍。适合从0开始的自增ID。
CHAR是固定长度，适合存储固定长度的密码、身份证号、手机号等。
不推荐使用TEXT和BLOB，如果VARCHAR能满足，尽量避免使用TEXT，而BLOB二进制存储基本不常用，因为这两种类型检索效率低、不能有默认值、不能直接创建索引（需指定前缀长度）。
时间建议使用TIMESTAMP，数据库指定固定时区，不依赖操作系统的时区。
不建议使用NULL作为默认值。

2. 代码案例

-- 创建一个示例数据库
CREATE DATABASE IF NOT EXISTS example_db;
USE example_db;

-- 创建一个示例表，涵盖各种数据类型，并设置默认值
CREATE TABLE example_table (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键，自动递增的整数',

    -- 整数类型字段
    tiny_number TINYINT DEFAULT 0 COMMENT '存储很小的整数，范围 -128 到 127（有符号），0 到 255（无符号），默认值为 0',
    small_number SMALLINT DEFAULT 0 COMMENT '存储小的整数，范围 -32,768 到 32,767（有符号），0 到 65,535（无符号），默认值为 0',
    medium_number MEDIUMINT DEFAULT 0 COMMENT '存储中等大小的整数，范围 -8,388,608 到 8,388,607（有符号），0 到 16,777,215（无符号），默认值为 0',
    normal_number INT DEFAULT 0 COMMENT '存储标准整数，范围 -2,147,483,648 到 2,147,483,647（有符号），0 到 4,294,967,295（无符号），默认值为 0',
    big_number BIGINT DEFAULT 0 COMMENT '存储大整数，范围 -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807（有符号），0 到 18,446,744,073,709,551,615（无符号），默认值为 0',

    -- 浮点数类型字段
    float_value FLOAT DEFAULT 0.0 COMMENT '单精度浮点数，默认值为 0.0',
    double_value DOUBLE DEFAULT 0.0 COMMENT '双精度浮点数，默认值为 0.0',

    -- 定点数类型字段
    decimal_value DECIMAL(10, 2) DEFAULT 0.00 COMMENT '精确的定点数，10 位数字总长度，其中 2 位是小数位，默认值为 0.00',

    -- 字符和字符串类型字段
    char_field CHAR(10) DEFAULT 'N/A' COMMENT '固定长度字符串，最多 10 个字符，默认值为 "N/A"',
    varchar_field VARCHAR(255) DEFAULT 'undefined' COMMENT '可变长度字符串，最多 255 个字符，默认值为 "undefined"',
    text_field TEXT DEFAULT 'No content' COMMENT '长文本，最大 65,535 字符，默认值为 "No content"',
    mediumtext_field MEDIUMTEXT DEFAULT 'No content' COMMENT '中等长度文本，最大 16,777,215 字符，默认值为 "No content"',
    longtext_field LONGTEXT DEFAULT 'No content' COMMENT '长文本，最大 4,294,967,295 字符，默认值为 "No content"',

    -- 二进制数据类型字段
    binary_field BINARY(10) DEFAULT 0x00 COMMENT '固定长度的二进制数据，最多 10 字节，默认值为 0x00',
    varbinary_field VARBINARY(255) DEFAULT 0x00 COMMENT '可变长度的二进制数据，最多 255 字节，默认值为 0x00',
    blob_field BLOB DEFAULT '' COMMENT '二进制大对象，最大 65,535 字节，默认值为空字符串',
    mediumblob_field MEDIUMBLOB DEFAULT '' COMMENT '中等长度的二进制大对象，最大 16,777,215 字节，默认值为空字符串',
    longblob_field LONGBLOB DEFAULT '' COMMENT '长二进制大对象，最大 4,294,967,295 字节，默认值为空字符串',

    -- 日期和时间类型字段
    date_field DATE DEFAULT '1970-01-01' COMMENT '存储日期，格式为 YYYY-MM-DD，范围从 1000-01-01 到 9999-12-31，默认值为 "1970-01-01"',
    time_field TIME DEFAULT '00:00

:00' COMMENT '存储时间，格式为 HH:MM:SS，范围从 -838:59:59 到 838:59:59，默认值为 "00:00:00"',
    datetime_field DATETIME DEFAULT '1970-01-01 00:00:00' COMMENT '存储日期和时间，格式为 YYYY-MM-DD HH:MM:SS，范围从 1000-01-01 00:00:00 到 9999-12-31 23:59:59，默认值为 "1970-01-01 00:00:00"',
    timestamp_field TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '存储时间戳，格式为 YYYY-MM-DD HH:MM:SS，范围从 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC。默认为当前时间，记录更新时自动更新时间',

    -- 布尔类型字段
    bool_field BOOLEAN DEFAULT FALSE COMMENT '布尔值，存储为 TINYINT(1)，0 表示 FALSE，非零值表示 TRUE，默认值为 FALSE',

    -- 枚举和集合类型字段
    enum_field ENUM('Value1', 'Value2', 'Value3') DEFAULT 'Value1' COMMENT '枚举类型，从预定义的选项中选择一个值，默认值为 "Value1"',
    set_field SET('Option1', 'Option2', 'Option3') DEFAULT 'Option1' COMMENT '集合类型，可以从预定义的选项中选择零个或多个值，默认值为 "Option1"'
    
) COMMENT='示例表，涵盖各种数据类型，并设置默认值';

-- 插入示例数据
INSERT INTO example_table (
    tiny_number, small_number, medium_number, normal_number, big_number,
    float_value, double_value, decimal_value,
    char_field, varchar_field, text_field, mediumtext_field, longtext_field,
    binary_field, varbinary_field, blob_field, mediumblob_field, longblob_field,
    date_field, time_field, datetime_field, timestamp_field,
    bool_field, enum_field, set_field
) VALUES (
    127, 32767, 8388607, 2147483647, 9223372036854775807,
    3.14, 3.14159265358979, 12345.67,
    'Fixed', 'Variable length string', 'This is a text field.', 'Medium text content.', 'Long text content goes here.',
    0x4F, 0x4F4F, 'Binary data', 'Medium binary data', 'Long binary data',
    '2024-08-15', '12:34:56', '2024-08-15 12:34:56', CURRENT_TIMESTAMP,
    TRUE, 'Value1', 'Option1,Option2'
);

3. 最佳实践

命名规范

数据库名称、主表和字段名称使用小写加下划线，如my_databases、my_tables、user_id
临时表使用tmp_为前缀，日期为后缀，如tmp_my_table_20240816
备份表使用bak_为前缀，日期为后缀，如bak_my_table_20240816

设计规范

使用InnoDB作为存储引擎
表、字段、索引等都加上注释
单表数量控制在500W，业务数据使用分库分表，日志数据用归档方案
采用物理分表的方式管理大量数据
经常一起使用的字段放到同一个表中
禁止在表中建立预留字段
禁止在数据库中存储图片、音视频，可以存它们的OSS连接地址
适当的反范式，通常满足第三范式即可
生产环境数据库严格控制权限，禁止线上数据库压力测试或从开发测试环境连接生产数据库
选择合适的数据类型，考虑空间占用，比如IP地址用数字类型
TEXT类型尽可能分离到单独扩展表中
避免使用ENUM类型，因为其ORDER BY操作效率低
字段尽可能定义为NOT NULL

索引规范

单张表索引不超过5个
禁止使用全文索引
禁止每一列都建立索引
表必须要有主键
避免建立冗余索引（如INDEX(a,b,c)、INDEX(a,b)）和重复索引（如PRIMARY KEY(id)、UNIQUE INDEX(id)）
对于频繁操作的查询，优先使用覆盖索引，以提高查询效率
频繁更新的字段不适合建立索引
尽可能考虑联合索引，而不是单列索引
删除长时间未使用的索引
避免索引失效：
- 未遵守最左匹配原则
- 索引列上计算
- 以%开头
- 使用了OR且前后条件有一个列没有索引
- 发生隐式转换

开发规范

尽量避免使用外键约束
不在数据库中做运算，复杂运算放在业务逻辑中
优化慢SQL
充分利用索引
禁用SELECT *

禁用不包含字段的INSERT，应使用如下形式：

INSERT INTO my_table(c1, c2, c3) VALUES ('a', 'b', 'c');

避免WHERE条件的数据类型隐式转换
避免使用子查询，尽可能优化为JOIN
避免JOIN关联太多的表
使用批量操作
少用OR
禁止使用ORDER BY RAND()随机排序
明显不会有重复时，使用UNION ALL
拆分大SQL为小SQL
禁止跨库查询，不同的数据库使用不同的账号连接

数据库操作

对于超过100W的批量修改（如UPDATE、INSERT等）操作，要分批多次进行
避免大事务操作
禁止把SUPER权限给应用程序
应用程序权限原则上不准有DROP权限

以上是MySQL的基础知识和最佳实践，希望对你的学习和使用有所帮助！

去1:1私密咨询

系列课程：

免费

VIP会员

第1课数据库_MySQL基础_最佳实践