creating database via bash with dash in database-name

Question

I try to create a new Database via bash which has a dash in the name.

Thats what I tried:

echo "CREATE DATABASE IF NOT EXISTS db-name CHARACTER SET utf8 COLLATE utf8_general_ci" | mysql -uuser -ppw

That fails with the following error:

ERROR 1064 (42000) at line 1: 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
'-name CHARACTER SET utf8 COLLATE utf8_general_ci' at line 1

I added backticks then:

echo "CREATE DATABASE IF NOT EXISTS `db-name` CHARACTER SET utf8 COLLATE utf8_general_ci" | mysql -uuser -ppw

ERROR 1064 (42000) at line 1: 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
'CHARACTER SET utf8 COLLATE utf8_general_ci' at line 1

I played around a bit and found out that mysql doesnt like backticks even without a dash in the name:

echo "CREATE DATABASE IF NOT EXISTS `dbname` CHARACTER SET utf8 COLLATE utf8_general_ci" | mysql -uuser -ppw

ERROR 1064 (42000) at line 1: 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
'CHARACTER SET utf8 COLLATE utf8_general_ci' at line 1

I am kinda confused. Whats wrong here?

PS: In phpmyadmin it works as expected when adding backticks

hek2mgl · Accepted Answer · 2015-03-05 15:40:54Z

55

Either you quote the backticks or simply use single quotes instead double quotes around the command:

mysql -uuser -ppw -e 'CREATE DATABASE IF NOT EXISTS `db-name` CHARACTER SET utf8 COLLATE utf8_general_ci'

Otherwise the shell would expand the backticks to a command substitution. Check this: http://tldp.org/LDP/abs/html/commandsub.html

Further note that you don't need the echo command. You can use the -e commandline option of mysql

edited Mar 5, 2015 at 15:40

answered Mar 5, 2015 at 15:35

hek2mgl

159k31 gold badges263 silver badges279 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Fuzzyma Over a year ago

I didnt know that backticks have to be escaped. Thank you!

Tam Over a year ago

any things wrong it did not work for me ? Command ---> mysql -uroot -pmysql -e 'CREATE DATABASE IF NOT EXISTS mifosplatform-tenants';

hek2mgl Over a year ago

Hard to say. I recommend opening a question if it still doesn't work for you

Alex K · Accepted Answer · 2018-05-22 14:28:45Z

4

Use with backslahes before backticks:

mysql -uuser -ppw -e 'CREATE DATABASE IF NOT EXISTS \`db-name\` CHARACTER SET utf8 COLLATE utf8_general_ci'

answered May 22, 2018 at 14:28

Alex K

3,3011 gold badge26 silver badges32 bronze badges

Comments

ESP32 · Accepted Answer · 2023-10-17 10:27:26Z

0

The correct way is to add back-ticks around the database name, like you do it with field names for example. Single quotes will not work usually.

CREATE DATABASE `db-name`;

answered Oct 17, 2023 at 10:27

ESP32

8,7732 gold badges46 silver badges71 bronze badges

Collectives™ on Stack Overflow

creating database via bash with dash in database-name

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related