Problems with mysql CREATE TABLE syntax-CodePudding

I try to create table in mysql with the following command:

CREATE TABLE keys (id INT(10), key VARCHAR(100));

and it always gives me an error like this:

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 'keys (id INT(10), key VARCHAR(100))' at line 1`

CodePudding user response：

So both the table name keys and the field key are reserved in the Mysql namespace. If you choose a different table name (e.g. keys_tbl) and rename the second field something like key_id, your code will work.

CodePudding user response：

You should be very carefully with table and columns naming, you might face a lot of problems in the future.

I suggest find names that are not MySQL reserved words

If you still want to keep the names you should put in in backticks.

mysql> CREATE TABLE keys (id INT(10), key VARCHAR(100));
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 'keys (id INT(10), key VARCHAR(100))' at line 1

mysql> CREATE TABLE `keys` (id INT(10), `key` VARCHAR(100));
Query OK, 0 rows affected, 1 warning (0.97 sec)

mysql> show create table keys;
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 'keys' at line 1
mysql>
mysql>
mysql> show create table `keys`;
 ------- ----------------------------------------------------------------------------------------------------------------------------------------------------- 
| Table | Create Table                                                                                                                                        |
 ------- ----------------------------------------------------------------------------------------------------------------------------------------------------- 
| keys  | CREATE TABLE `keys` (
  `id` int DEFAULT NULL,
  `key` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
 ------- ----------------------------------------------------------------------------------------------------------------------------------------------------- 
1 row in set (0.01 sec)

Note that you have to use backticks when using the table