在实际开发中，当数据量逐渐增大时，单一数据库和表可能无法满足性能需求。分库分表是一种有效的解决方案，可以将数据分散存储到多个数据库和表中，提高系统的性能和可扩展性。本文将介绍如何编写一个批量创建分库分表的 MySQL 脚本，帮助您快速搭建分库分表架构。​

一、脚本功能概述​

这个 MySQL 脚本实现了以下功能：​

  1. 数据库连接配置：设置数据库连接参数，包括主机地址、端口、用户名和密码。​
  2. 外层循环创建数据库：通过循环创建 8 个数据库，命名为user_0到user_7。
  3. ​内层循环创建表：每个数据库内创建 16 张表，表名格式为user_info_0到user_info_127。
  4. ​表结构定义：每个表都包含相同的表结构，包括用户基本信息字段和索引。​

最终将创建8 个数据库 × 16 张表 = 128 张表的分库分表架构。​

二、完整脚本代码

#!/bin/bash

# -------------------------- 数据库连接配置 --------------------------
# 根据实际环境修改以下参数
MYSQL_HOST="127.0.0.1"   # MySQL主机地址
MYSQL_PORT="3306"        # MySQL端口
MYSQL_USER="root"        # 连接用户名（需有创建库表权限）
MYSQL_PASSWORD="rootpass"# 连接密码
# -------------------------------------------------------------------

# 外层循环：创建8个数据库（user_0 到 user_7）
for db_idx in $(seq 0 7); do
    db_name="user_${db_idx}"
    echo "开始创建数据库：${db_name} (${db_idx}/7)"

    # 使用Here Document执行SQL命令
    mysql -h"${MYSQL_HOST}" -P"${MYSQL_PORT}" -u"${MYSQL_USER}" -p"${MYSQL_PASSWORD}" << EOF
        -- 创建数据库（指定字符集为utf8mb4，支持emoji）
        CREATE DATABASE IF NOT EXISTS ${db_name} 
        CHARACTER SET utf8mb4 
        COLLATE utf8mb4_unicode_ci;

        -- 切换到当前数据库
        USE ${db_name};

        -- 内层循环：每个数据库创建16张表（user_info_0 到 user_info_15）
EOF

    # 内层循环：生成16张表的创建语句
    for i in $(seq 0 15); do
        table_idx=$((db_idx * 16 + i))
        echo "  正在创建表：user_info_${table_idx} (${i}/15)"
        
        # 执行建表SQL
        mysql -h"${MYSQL_HOST}" -P"${MYSQL_PORT}" -u"${MYSQL_USER}" -p"${MYSQL_PASSWORD}" "${db_name}" << EOF
            CREATE TABLE IF NOT EXISTS user_info_${table_idx} (
                id VARCHAR(36) NOT NULL COMMENT '用户ID（UUID）',
                name VARCHAR(50) NOT NULL COMMENT '用户名',
                phone VARCHAR(20) NOT NULL COMMENT '手机号',
                age TINYINT UNSIGNED DEFAULT NULL COMMENT '年龄（无符号，0-255）',
                school VARCHAR(100) DEFAULT NULL COMMENT '学校',
                gender TINYINT(1) DEFAULT NULL COMMENT '性别：0-女，1-男，2-未知',
                create_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                PRIMARY KEY (id),  -- 主键（唯一且非空）
                UNIQUE KEY uni_name (name),  -- 用户名唯一索引（避免重复）
                KEY idx_id_name_phone (id, name, phone)  -- 组合索引（优化多字段查询）
            ) ENGINE=InnoDB 
              DEFAULT CHARSET=utf8mb4 
              COMMENT='用户信息表（分片索引：${table_idx}）';
EOF
    done

    echo "数据库 ${db_name} 创建完成！"
    echo "----------------------------------------"
done

echo "所有分库分表创建完毕！"

三、脚本执行输出示例​

当您运行上述脚本时，会在终端看到类似以下的输出：

开始创建数据库：user_0 (0/7)
  正在创建表：user_info_0 (0/15)
  正在创建表：user_info_1 (1/15)
  正在创建表：user_info_2 (2/15)
  正在创建表：user_info_3 (3/15)
  正在创建表：user_info_4 (4/15)
  正在创建表：user_info_5 (5/15)
  正在创建表：user_info_6 (6/15)
  正在创建表：user_info_7 (7/15)
  正在创建表：user_info_8 (8/15)
  正在创建表：user_info_9 (9/15)
  正在创建表：user_info_10 (10/15)
  正在创建表：user_info_11 (11/15)
  正在创建表：user_info_12 (12/15)
  正在创建表：user_info_13 (13/15)
  正在创建表：user_info_14 (14/15)
  正在创建表：user_info_15 (15/15)
数据库 user_0 创建完成！
----------------------------------------
开始创建数据库：user_1 (1/7)
  正在创建表：user_info_16 (0/15)
  正在创建表：user_info_17 (1/15)
...
所有分库分表创建完毕！

这种输出格式清晰地显示了脚本的执行进度，包括当前正在创建的数据库和表，以及完成的百分比。​

四、脚本详细说明​

4.1 数据库连接配置​

脚本开头部分定义了数据库连接参数：

MYSQL_HOST="127.0.0.1"
MYSQL_PORT="3306"
MYSQL_USER="root"
MYSQL_PASSWORD="rootpass"

请务必根据实际环境修改这些参数，特别是：​

  • MYSQL_HOST：数据库服务器的 IP 地址或域名​
  • MYSQL_PORT：数据库端口，默认是 3306​
  • MYSQL_USER：具有创建数据库和表权限的用户名​
  • MYSQL_PASSWORD：对应的密码​

4.2 外层循环创建数据库​

外层循环使用seq 0 7生成 0 到 7 的索引，创建 8 个数据库：

for db_idx in $(seq 0 7); do
    db_name="user_${db_idx}"
    echo "开始创建数据库：${db_name} (${db_idx}/7)"
    # ... 创建数据库的代码 ...
done

每个数据库名称遵循user_0到user_7的命名规则。echo语句用于显示当前正在创建的数据库及其进度。​

4.3 使用 Here Document 创建数据库​

使用 MySQL 命令行工具和 Here Document 语法执行创建数据库的 SQL 命令：

mysql -h"${MYSQL_HOST}" -P"${MYSQL_PORT}" -u"${MYSQL_USER}" -p"${MYSQL_PASSWORD}" << EOF
    CREATE DATABASE IF NOT EXISTS ${db_name} 
    CHARACTER SET utf8mb4 
    COLLATE utf8mb4_unicode_ci;
    USE ${db_name};
EOF

IF NOT EXISTS子句确保如果数据库已存在，不会产生错误。使用utf8mb4字符集支持包括 emoji 在内的所有 Unicode 字符。​

4.4 内层循环创建表​

内层循环在每个数据库中创建 16 张表：

for i in $(seq 0 15); do
    table_idx=$((db_idx * 16 + i))
    echo "  正在创建表：user_info_${table_idx} (${i}/15)"
    # ... 创建表的代码 ...
done

表索引通过公式table_idx = db_idx * 16 + i计算得出，确保每个表在整个架构中具有唯一的索引（从 0 到 127）。​

4.5 表结构定义​

每个表都具有相同的结构定义：

CREATE TABLE IF NOT EXISTS user_info_${table_idx} (
    id VARCHAR(36) NOT NULL COMMENT '用户ID（UUID）',
    name VARCHAR(50) NOT NULL COMMENT '用户名',
    phone VARCHAR(20) NOT NULL COMMENT '手机号',
    age TINYINT UNSIGNED DEFAULT NULL COMMENT '年龄（无符号，0-255）',
    school VARCHAR(100) DEFAULT NULL COMMENT '学校',
    gender TINYINT(1) DEFAULT NULL COMMENT '性别：0-女，1-男，2-未知',
    create_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (id),
    UNIQUE KEY uni_name (name),
    KEY idx_id_name_phone (id, name, phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表（分片索引：${table_idx}）';

表结构详细说明：​

     字段定义：​

  • id：使用 VARCHAR (36) 存储 UUID，作为主键​
  • name：VARCHAR (50) 存储用户名，不能为空​
  • phone：VARCHAR (20) 存储手机号，不能为空​
  • age：TINYINT UNSIGNED 类型，范围 0-255​
  • school：VARCHAR (100) 存储学校信息​
  • gender：TINYINT (1) 存储性别，0 表示女，1 表示男，2 表示未知​
  • create_at：TIMESTAMP 类型，默认值为当前时间
  • 索引设计：​
  • PRIMARY KEY (id)：主键索引，确保 id 唯一且非空​
  • UNIQUE KEY uni_name (name)：唯一索引，确保用户名唯一​
  • KEY idx_id_name_phone (id, name, phone)：组合索引，优化多字段查询​
  1. 存储引擎和字符集：​
  • 使用ENGINE=InnoDB存储引擎，支持事务和行级锁​
  • DEFAULT CHARSET=utf8mb4指定字符集为 utf8mb4，支持所有 Unicode 字符​

随着数据量的增长，单库单表会面临以下问题：​

  • 查询性能下降​
  • 写入性能下降​
  • 备份和恢复时间增加​
  • 硬件资源利用率低​

分库分表通过将数据分散存储到多个数据库和表中，可以有效解决这些问题，提高系统的可扩展性和性能。​

 本脚本采用的分库分表策略​

本脚本实现了一种简单的水平分库分表策略：​

  1. 水平分库：将数据分散到多个数据库（user_0 到 user_7）​
  1. 水平分表：每个数据库中再分散到多个表（user_info_0 到 user_info_15）​
  1. 分片键：通过某种算法（如哈希、取模等）将数据映射到具体的数据库和表​

分片键计算公式：​

​

TypeScript取消自动换行复制

数据库索引 = 分片键 % 数据库数量​

表索引 = (分片键 / 数据库数量) % 表数量​

​

在实际应用中，需要根据业务特点选择合适的分片键。常见的分片键包括：​

  • 用户 ID​
  • 时间戳​
  • 地理位置​
  • 业务类型​

 分库分表带来的挑战​

分库分表虽然解决了单库单表的性能问题，但也带来了一些新的挑战：​

  1. 分布式事务：跨库操作时，事务处理变得复杂​
  1. 跨库关联查询：无法直接进行 JOIN 操作，需要在应用层处理​
  1. 分布式 ID：需要保证全局唯一的 ID 生成策略​
  1. 数据迁移与扩容：随着数据增长，可能需要重新分片​

总结​

本文介绍了如何编写一个批量创建分库分表的 MySQL 脚本，该脚本可以创建 8 个数据库，每个数据库包含 16 张表，总共 128 张表的分库分表架构。​

关键点包括：​

  1. 使用嵌套循环创建数据库和表​
  1. 采用 utf8mb4 字符集支持所有 Unicode 字符​
  1. 合理设计表结构和索引​
  1. 使用 Here Document 语法执行批量 SQL 命令​

分库分表是解决大数据量存储和高性能需求的有效手段，但也需要根据具体业务场景选择合适的分库分表策略，并注意处理随之而来的各种挑战。​

通过本脚本，您可以快速搭建一个分库分表的数据库架构，为后续的数据分片存储奠定基础。实际应用中，可以根据业务数据量调整分库分表数量，以达到最佳的性能和资源利用率。

# 数据库 # mysql
Logo
CSDN-OPC开发者社区

这里是“一人公司”的成长家园。我们提供从产品曝光、技术变现到法律财税的全栈内容，并连接云服务、办公空间等稀缺资源，助你专注创造，无忧运营。

更多推荐

cover

『低代码开发的终极形态：AI 驱动的可视化编程』

avatar CSDN-OPC开发者社区
cover

上下文工程完全指南：打造高确定性AI Agent的四大支柱（建议收藏）

avatar CSDN-OPC开发者社区

Python内存管理终极指南：优化大型数据集处理性能的5个技巧

Python内存管理在处理大型数据集时常常成为性能瓶颈，掌握有效的内存优化技巧能让你的程序运行速度提升数倍。在python-mastery课程中，我们深入探讨了如何通过不同的数据结构选择来显著降低内存使用。## 🚀 为什么Python内存管理如此重要？当处理像芝加哥公交系统数据（包含577,000多行记录）这样的大型数据集时，内存使用可能从12MB激增到50MB以上！这不仅仅是数字问题，

avatar CSDN-OPC开发者社区