Any way of using fully qualified table name with CREATE TABLE

Question

When I use CREATE TABLE tbl_name I can only specify a table in database I currently use. Is there any way to CREATE TABLE tbl_name in database1 without a prior USE database1?

Answer 1

Try:

mysql> CREATE DATABASE `test_1`;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE DATABASE `test_2`;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `test_1`.`table_1` (`column_1` BOOL);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `test_2`.`table_2` (`column_1` BOOL);
Query OK, 0 rows affected (0.01 sec)

mysql> USE `test_1`;
Database changed

mysql> SHOW TABLES;
+------------------+
| Tables_in_test_1 |
+------------------+
| table_1          |
+------------------+
1 row in set (0.00 sec)

mysql> USE `test_2`;
Database changed

mysql> SHOW TABLES;
+------------------+
| Tables_in_test_2 |
+------------------+
| table_2          |
+------------------+
1 row in set (0.00 sec)