Bulk create thousands of mysql database giving errors [closed]

by

I need to create thousands of database such like below:

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;

usually MySQL will giving an error on some of the commands:

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

another error:

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

Is there any way to bulk create database without error?
I thought using && will works but I have no idea how to use it in the command.

Answer

Yes it is your fault. MySQL does not do things without being told to.

I have absolutely no problem creating 999 databases within a second: 

/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

And just to look at what I have in my 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;

I’m sure that you are doing something wrong. Put your statements in a file and then execute that file in MySQL. If you are trying to paste it in the MySQL console, something could be wrong with your buffer or your connection causing that behavior.

Attribution
Source : Link , Question Author : Maka mikuni , Answer Author : Frands Hansen

Leave a Comment