Skip to content

How to Create Databases with Names Containing Special Characters

MySQL allows you to create databases with names containing special characters. But if you create it without taking care of the name, it will fail to create due to syntax error. Let's see the problem:

Names containing Dot, At or Ampersand are not accepted in normal creations:

mysql> CREATE DATABASE db.mysql DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
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 '.mysql DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci' at line 1
mysql> CREATE DATABASE db@mysql DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
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 '@mysql DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci' at line 1
mysql> CREATE DATABASE db&mysql DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
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 '&mysql DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci' at line 1

Single Quotes and Double Quotes can not help:

mysql> CREATE DATABASE 'db&mysql' DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
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 ''db&mysql' DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci' at line 1
mysql> CREATE DATABASE "db&mysql" DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
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 '"db&mysql" DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci' at line 1

The solution is to wrap the name by Back Quotes:

mysql> CREATE DATABASE `db&mysql` DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql>

Let's see the result.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db&mysql           |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

Leave a Reply

Your email address will not be published. Required fields are marked *