-10

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.

Improve this question
4
  • 2
    Huh? Your first error is because you've got "CREATE D" on the first line trying to run as part of the same query; second one is not an error at all ("Query OK").
    – Shane Madden
    Jun 19, 2011 at 7:31
  • This is not my fault. CREATE D is generated because MySQL dont delay between query, whether its process-executed delay or time delay. Are you newbie of MySQL? Every bits of my codes are right. Second is running fine but there is ; generated. Try yourself first before commenting
    – Maka mikuni
    Jun 19, 2011 at 7:34
  • 3
    If "every bits" of your code is right, why do you think you are getting errors? Why do you think MySQL should be creating delays?
    – John Gardeniers
    Jun 19, 2011 at 8:53
  • Would you mind showing the code that generates the statements? There's no way to tell why this is happening (You might want to consider goingt to stackoverflow with a question about the bug in your code, you're clearly generating bad statements)
    – serverhorror
    Jun 19, 2011 at 11:03

2 Answers 2

Reset to default
3

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.

Improve this answer
3

I knocked up the script below which generates a file with 25000 CREATE DATABASE ... statements.

#!/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

sample output

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;

I used the command 

mysql -u root -p <dbgen.sql

and it creates 25000 databases in about 5 minutes without any problems. What command are running to do the job ?

Improve this answer

Not the answer you're looking for? Browse other questions tagged .