mysql - 是什么导致此 MySQL 查询中的语法错误？

Question

为什么我会收到错误消息？

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 'read, compose, reply, moderate FROM article_permissions' at line 2

SELECT permission_id, category_id, group_id, read, compose, reply, moderateFROM article_permissionsWHERE category_id = 6     AND group_id = 0 ORDER BY permission_id DESC LIMIT 1

这是表格:

CREATE TABLE IF NOT EXISTS `article_permissions` (  `permission_id` int(15) NOT NULL AUTO_INCREMENT,  `category_id` int(15) NOT NULL,  `group_id` int(15) NOT NULL,  `read` tinyint(1) NOT NULL,  `compose` tinyint(1) NOT NULL,  `reply` tinyint(1) NOT NULL,  `moderate` tinyint(1) NOT NULL,  PRIMARY KEY (`permission_id`)) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

Answer 1

read 是一个 reserved word对于 MySQL。要将其用作查询中的列名，请将其放在反引号中(就像在表创建语句中一样)。

SELECT 
`permission_id`, `category_id`, `group_id`, `read`, `compose`, `reply`, `moderate`
FROM article_permissions
WHERE `category_id` = 6
     AND `group_id` = 0 
ORDER BY `permission_id` DESC 
LIMIT 1

(如果启用 ANSI_QUOTES option ，则可以使用双引号代替，这样更便于携带。)