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

Answer 1

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

Answer 2

Use with backslahes before backticks:

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