mysql字段是关键字_MySql 表的一个字段的字段名和关键字重名

今天遇到一个问题就是表的一个字段的字段名和关键字重名，我先创建了一个uses数据库

mysql> create database users;

Query OK, 1 row affected (0.00 sec)

mysql> use users

Database changed

mysql> show tables;

Empty set (0.00 sec)

然后新建了一个userinfo表

mysql> CREATE TABLE userinfo(

-> id INT NOT NULL AUTO_INCREMENT,

-> `name` VARCHAR(45) NOT NULL,

-> `password` VARCHAR(45) NOT NULL,

-> remark VARCHAR(100) NOT NULL,

-> PRIMARY KEY(id)

-> );

Query OK, 0 rows affected (0.06 sec)

mysql> show tables;

+-----------------+

| Tables_in_users |

+-----------------+

|

userinfo |

+-----------------+

1 row in set (0.00 sec)

mysql> DESCRIBE userinfo;

+----------+--------------+------+-----+---------+----------------+

| Field |

Type | Null | Key | Default |

Extra |

+----------+--------------+------+-----+---------+----------------+

|

id |

int(11) | NO | PRI |

NULL |

auto_increment |

|

name | varchar(45) |

NO | | NULL | |

| password | varchar(45) |

NO | | NULL | |

| remark | varchar(100) |

NO | | NULL | |

+----------+--------------+------+-----+---------+----------------+

4 rows in set (0.01 sec)

接着我想给表里面插入一条记录，如下所示：

mysql> INSERT INTO

userinfo('name','password',remark) VALUES

('Tom','12345','good');

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 ''name','password',remark) VALUES

('Tom','12345','good')' at line 1

这时候问题出现了，以前用MSSQL Server时，用中括号就可以解决。

mysql> INSERT INTO

userinfo([name],[password],remark) VALUES

('Johnson','12345','good');

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 '[name],[password],remark) VALUES

('Johnson','12345','good')' at line 1

最后经过把name和password两边的单引号改成反引号(数字键1旁边ESC下面的那个键)就好了

mysql> INSERT INTO

userinfo(`name`,`password`,remark) VALUES

('Tom','12345','good');

Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM userinfo;

+----+---------+----------+--------+

| id | name |

password | remark |

+----+---------+----------+--------+

| 1 |

Tom | 12345 |

good |

| 2 | Johnson |

12345 |

good |

+----+---------+----------+--------+

2 rows in set (0.00 sec)

发现和MSSQL Server不一样，MSSQL Server用中括号就可以解决。