MySQL反引号的使用姿势

反引号，一般在ESC键的下方。它是为了区分MySQL的保留字与普通字符而引入的符号。举个例子：

SELECT `select` FROM `test` WHERE select='字段值'

1	SELECT `select` FROM `test` WHERE select='字段值'

在test表中，有个select字段，如果不用反引号，MYSQL将把select视为保留字而导致出错，所以，有MYSQL保留字作为字段的，必须加上反引号来区分。引号一般用在字段的值,如果字段值是字符或字符串，则要加引号，如：select=’字段值’。不加反引号建的表不能包含MYSQL保留字，否则出错。用普通引号，当输入左引号时，后面的内容因为引号没有关闭而默认作为引号内的内容，用普通引号括起来的内容为绿色。我们建表的时候一般都会将表名，库名都加上反引号来保证语句的执行度。反引号`，数字1左边的符号。保留字不能用于表名，比如desc，建表时不能用于字段名，表名:

mysql> create table desc (id varchar(255));
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 'desc (id varchar(255))' at line 1

1 2	mysql> create table desc (id varchar(255)); 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 'desc (id varchar(255))' at line 1

mysql> create table test3(desc varchar(255));
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 'desc varchar(255))' at line 1

1 2	mysql> create table test3(desc varchar(255)); 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 'desc varchar(255))' at line 1

但将desc放到反引号中则正常。

mysql> create table `desc` (id varchar(255));
Query OK, 0 rows affected (0.01 sec)
mysql> create table test3 (`desc` varchar(255));
Query OK, 0 rows affected (0.00 sec)

mysql> create table `desc` (id varchar(255));

Query OK, 0 rows affected (0.01 sec)

mysql> create table test3 (`desc` varchar(255));

Query OK, 0 rows affected (0.00 sec)

有些管理员在建表时，为了省事，可能会选择从一个现有的表cp建表语句。修改创建、字段命名如果不规范的话，可能会用到mysql中的保留字，如上述的desc。

例如从show create table xxx 或者mysqldump –no-data中取出建表语句，形式如下:

CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL auto_increment,
`time` datetime NOT NULL default '0000-00-00 00:00:00',
`type` int(10) unsigned NOT NULL default '0',
`desc` varchar(255) default NULL,
primary key `i_id` (`id`)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE `test` (

`id` int(10) unsigned NOT NULL auto_increment,

`time` datetime NOT NULL default '0000-00-00 00:00:00',

`type` int(10) unsigned NOT NULL default '0',

`desc` varchar(255) default NULL,

primary key `i_id` (`id`)

) ENGINE=innodb DEFAULT CHARSET=latin1;

创建成功后,在insert时如果不带反引号就会遇到问题：

mysql> insert into test(`time`,`type`,`desc`) values(now(),5,'aa');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test(time,type,desc) values(now(),5,'aa');
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 'desc) values(now(),5,'aa')' at line 1

mysql> insert into test(`time`,`type`,`desc`) values(now(),5,'aa');

Query OK, 1 row affected (0.01 sec)

mysql> insert into test(time,type,desc) values(now(),5,'aa');

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 'desc) values(now(),5,'aa')' at line 1

针对这个问题,有2个建议

1) 在建表时,不要用保留字作表名,字段名.可以加一个前缀标志加以区分,如在表名前加t_, 在列名前加F。

2) 建表时,不要给表名/字段名/索引名加反引号,以免隐藏问题。

如果您觉得本站对你有帮助，那么可以支付宝扫码捐助以帮助本站更好地发展，在此谢过。