mysql如何批量创建多个数据库_mysql批量创建数据库的高效方案

mysql

.sql

CREATE DATABASE

-- create_databases.sql
CREATE DATABASE IF NOT EXISTS my_db_001 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS my_db_002 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS my_db_003 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- ... 更多数据库，例如到 my_db_100
CREATE DATABASE IF NOT EXISTS my_db_100 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

mysql -u your_username -p < create_databases.sql

IF NOT EXISTS

#!/bin/bash
# generate_and_create.sh

DB_PREFIX="project_db_"
START_NUM=1
END_NUM=20 # 假设我们要创建 project_db_001 到 project_db_020

SQL_FILE="batch_create_dbs.sql"

# 清空或创建SQL文件
> $SQL_FILE

for i in $(seq $START_NUM $END_NUM); do
    DB_NAME="${DB_PREFIX}$(printf "%03d" $i)" # 格式化为三位数，例如 project_db_001
    echo "CREATE DATABASE IF NOT EXISTS $DB_NAME CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;" >> $SQL_FILE
done

echo "Generated SQL file: $SQL_FILE"
echo "Executing SQL file..."

# 执行生成的SQL文件
# 替换 your_username 为你的MySQL用户名
mysql -u your_username -p < $SQL_FILE

echo "Batch database creation complete."

CREATE DATABASE

mysql

DB_PREFIX

START_NUM

END_NUM

mysql-connector-python

pip install mysql-connector-python

import mysql.connector

# 数据库连接信息
config = {
    'user': 'your_username',
    'password': 'your_password',
    'host': '127.0.0.1',
    'database': '' # 连接时不需要指定数据库，因为我们要创建数据库
}

db_prefix = "app_db_"
start_num = 1
end_num = 10 # 创建 app_db_001 到 app_db_010

try:
    # 连接到MySQL服务器
    cnx = mysql.connector.connect(**config)
    cursor = cnx.cursor()

    for i in range(start_num, end_num + 1):
        db_name = f"{db_prefix}{i:03d}" # 格式化为三位数
        create_db_query = f"CREATE DATABASE IF NOT EXISTS {db_name} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

        try:
            cursor.execute(create_db_query)
            print(f"Database '{db_name}' created successfully.")
        except mysql.connector.Error as err:
            print(f"Failed to create database '{db_name}': {err}")

    # 提交事务（虽然CREATE DATABASE通常是DDL，自动提交，但显式提交是好习惯）
    cnx.commit()

except mysql.connector.Error as err:
    if err.errno == mysql.connector.errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif err.errno == mysql.connector.errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)
finally:
    if 'cnx' in locals() and cnx.is_connected():
        cursor.close()
        cnx.close()
        print("MySQL connection closed.")

CREATE DATABASE

CREATE

ALL PRIVILEGES

root

my_db_001

user_001

my_db_001

SELECT, INSERT, UPDATE, DELETE

-- 示例：创建数据库后，创建用户并授权
CREATE DATABASE IF NOT EXISTS my_db_001 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 创建用户，并限制只能从特定IP连接，例如 'localhost'
CREATE USER 'user_001'@'localhost' IDENTIFIED BY 'strong_password_001';
-- 或者如果你需要从任何主机连接（不推荐在生产环境中使用 %）
-- CREATE USER 'user_001'@'%' IDENTIFIED BY 'strong_password_001';

--