☰

批量创建数千个mysql数据库出现错误

tag-icon • tag-icon mysql database
批量创建数千个mysql数据库出现错误

我需要创建数千个如下所示的数据库：

CREATE DATABASE adr0711 CHARACTER SET utf8 COLLATE utf8_bin;
CREATE DATABASE adr0712 CHARACTER SET utf8 COLLATE utf8_bin;
CREATE DATABASE adr0713 CHARACTER SET utf8 COLLATE utf8_bin;
CREATE DATABASE adr0714 CHARACTER SET utf8 COLLATE utf8_bin;
CREATE DATABASE adr0715 CHARACTER SET utf8 COLLATE utf8_bin;
CREATE DATABASE adr0717 CHARACTER SET utf8 COLLATE utf8_bin;
CREATE DATABASE adr0718 CHARACTER SET utf8 COLLATE utf8_bin;

通常 MySQL 会对某些命令给出错误：

mysql> CREATE D
    -> CREATE DATABASE adr0653 CHARACTER SET utf8 COLLATE utf8_bin;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'D
CREATE DATABASE adr0653 CHARACTER SET utf8 COLLATE utf8_bin' at line 1

另一个错误：

mysql> CREATE DATABASE adr0614 CHARACTER SET utf8 COLLATE utf8_bin;
;
Query OK, 1 row affected (0.02 sec)

有没有办法批量创建数据库而不会出错？我以为使用&&will 可以，但我不知道如何在命令中使用它。

答案1

是的，这是你的错。MySQL 不会在无人指令的情况下执行操作。

我在一秒钟内创建 999 个数据库绝对没有问题：

/tmp # wc -l db.sql 
999 db.sql

/tmp # mysql -u root -p < db.sql 
Enter password: 

/tmp # echo "SHOW DATABASES; "|mysql -u root -p
Enter password: 
Database
information_schema
adr01
adr010
adr0100
adr0101
adr0102
adr0103
adr0104
adr0105

看看我的 db.sql 中有什么：

/tmp # tail -20 db.sql 
CREATE DATABASE adr0980 CHARACTER SET utf8 COLLATE utf8_bin;
CREATE DATABASE adr0981 CHARACTER SET utf8 COLLATE utf8_bin;
CREATE DATABASE adr0982 CHARACTER SET utf8 COLLATE utf8_bin;
CREATE DATABASE adr0983 CHARACTER SET utf8 COLLATE utf8_bin;
CREATE DATABASE adr0984 CHARACTER SET utf8 COLLATE utf8_bin;
CREATE DATABASE adr0985 CHARACTER SET utf8 COLLATE utf8_bin;
CREATE DATABASE adr0986 CHARACTER SET utf8 COLLATE utf8_bin;
CREATE DATABASE adr0987 CHARACTER SET utf8 COLLATE utf8_bin;

我确信你做错了什么。将你的语句放入一个文件中，然后在 MySQL 中执行该文件。如果你尝试将其粘贴到 MySQL 控制台中，则可能是你的缓冲区或连接出了问题，导致了这种行为。

答案2

我编写了下面的脚本，该脚本生成一个包含 25000 个 CREATE DATABASE ... 语句的文件。

#!/bin/bash
dnamec=0

while [ $dnamec -le 25000 ]
do
    echo "CREATE DATABASE adr$dnamec CHARACTER SET utf8 COLLATE utf8_bin;" >>dbgen.sql
    echo "DROP DATABASE adr$dnamec ;" >> cleanup.sql
    let dnamec=$dnamec+1
done

示例输出

CREATE DATABASE adr0 CHARACTER SET utf8 COLLATE utf8_bin;
CREATE DATABASE adr1 CHARACTER SET utf8 COLLATE utf8_bin;
CREATE DATABASE adr2 CHARACTER SET utf8 COLLATE utf8_bin;
.
.
.
CREATE DATABASE adr24998 CHARACTER SET utf8 COLLATE utf8_bin;
CREATE DATABASE adr24999 CHARACTER SET utf8 COLLATE utf8_bin;
CREATE DATABASE adr25000 CHARACTER SET utf8 COLLATE utf8_bin;

我使用了命令

mysql -u root -p <dbgen.sql

它在大约 5 分钟内创建了 25000 个数据库，没有任何问题。运行什么命令来完成这项工作？

相关内容