在mysql中重命名名为"add“的列

Question

我从来没有考虑过它的含义，我在mysql中创建了一个名为"add“的专栏。(显然，我在创建表时并没有想到add是一个保留字)，而在每次查询时，这被证明是一个问题。我已决定更改名称，但现在也无法更改。

我尝试了以下变体:都不起作用

mysql> alter table ml_n1 m modify column m.add addmovie tinyint(4);                                 
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 'm modify column m.add addmovie tinyint(4)' at line 1

mysql> alter table ml_n1 modify column add addmovie tinyint(4);
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 'add addmovie tinyint(4)' at line 1

mysql> alter table ml_n1 modify column 'add' 'addmovie tinyint(4)';
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 ''add' 'addmovie tinyint(4)'' at line 1

mysql> alter table ml_n1 modify column 'add' addmovie tinyint(4);
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 ''add' addmovie tinyint(4)' at line 1

mysql> alter table ml_n1 change add addmovie tinyint(4);
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 'add addmovie tinyint(4)' at line 1

mysql> alter table ml_n1 change 'add' addmovie tinyint(4);
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 ''add' addmovie tinyint(4)' at line 1

有人能建议一个变通办法吗？

Answer 1

ALTER TABLE ml_n1 CHANGE COLUMN `add` addmovie TINYINT(4)

正如您所暗示的；调用列add是有问题的，因为add是MySQL中的保留字，为了引用列，您需要用反号对其进行转义，`。

您需要使用CHANGE而不是MODIFY来更改列名。

来自the docs的N.B.

使用CHANGE或MODIFY时，column_definition必须包括应应用于新列的数据类型和所有属性，而不包括主键或唯一等索引属性。

我不认为这是一种变通办法。

Answer 2

alter table ml_n1 change `add` addmovie tinyint(4);

Answer 3

以下内容应该适用于您：

ALTER TABLE `ml_n1` CHANGE COLUMN `add` `addmovie` tinyint(4);

反划线允许您引用包含保留字或空格等的列名。

我个人养成了在使用MySQL编码时在表和列引用两边加上反号的习惯。