mysql 报错ERROR 1064 (42000)，原因使用了mysql保留字

来源：互联网发布：电视墙控制软件编辑：程序博客网时间：2024/06/06 03:07

执行select语句：

select * from cfg_parameter where key='nSJtifqVSI7HkPrKHlxhD6';
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 'key='nSJtifqVSI7HkPrKHlxhD6'' at line 1

乍一看，好像这条语句并没有什么问题。但是执行之后mysql确实报错了，所以肯定是有问题的。

排查：select * from cfg_parameter where 没有问题，key也没问题，='nSJtifqVSI7HkPrKHlxhD6';也没有问题。

等等，再仔细看看，发现key是mysql的保留字，当使用了mysql保留字，需要用反引号将其引起来，也就是键盘数字1旁边的键（` `）。

解决方法：

1、若使用了mysql的保留字，需要将其用反引号引起来。

mysql> select * from cfg_parameter where `key`='nSJtifqVSI7HkPrKHlxhD6';
+------+------------------------+----------------------+-----------+-----------------------------------------------------------+------------+
| id | key | cfg_name | option_id | parameter_content | is_deleted |
+------+------------------------+----------------------+-----------+-----------------------------------------------------------+------------+
| 5 | nSJtifqVSI7HkPrKHlxhD6 | ACTIONID_LIST_TO_RCP | NULL | {"ACTION_IDS":"0~90001"} | 0 |
+------+------------------------+----------------------+-----------+-----------------------------------------------------------+------------+

2、建议不要在创建表的过程中使用mysql保留字，避免后期造成麻烦。

mysql保留字：

ADDALLALTERANALYZEANDASASCASENSITIVEBEFOREBETWEENBIGINTBINARYBLOBBOTHBYCALLCASCADECASECHANGECHARCHARACTERCHECKCOLLATECOLUMNCONDITIONCONNECTIONCONSTRAINTCONTINUECONVERTCREATECROSSCURRENT_DATECURRENT_TIMECURRENT_TIMESTAMPCURRENT_USERCURSORDATABASEDATABASESDAY_HOURDAY_MICROSECONDDAY_MINUTEDAY_SECONDDECDECIMALDECLAREDEFAULTDELAYEDDELETEDESCDESCRIBEDETERMINISTICDISTINCTDISTINCTROWDIVDOUBLEDROPDUALEACHELSEELSEIFENCLOSEDESCAPEDEXISTSEXITEXPLAINFALSEFETCHFLOATFLOAT4FLOAT8FORFORCEFOREIGNFROMFULLTEXTGOTOGRANTGROUPHAVINGHIGH_PRIORITYHOUR_MICROSECONDHOUR_MINUTEHOUR_SECONDIFIGNOREININDEXINFILEINNERINOUTINSENSITIVEINSERTINTINT1INT2INT3INT4INT8INTEGERINTERVALINTOISITERATEJOINKEYKEYSKILLLABELLEADINGLEAVELEFTLIKELIMITLINEARLINESLOADLOCALTIMELOCALTIMESTAMPLOCKLONGLONGBLOBLONGTEXTLOOPLOW_PRIORITYMATCHMEDIUMBLOBMEDIUMINTMEDIUMTEXTMIDDLEINTMINUTE_MICROSECONDMINUTE_SECONDMODMODIFIESNATURALNOTNO_WRITE_TO_BINLOGNULLNUMERICONOPTIMIZEOPTIONOPTIONALLYORORDEROUTOUTEROUTFILEPRECISIONPRIMARYPROCEDUREPURGERAID0RANGEREADREADSREALREFERENCESREGEXPRELEASERENAMEREPEATREPLACEREQUIRERESTRICTRETURNREVOKERIGHTRLIKESCHEMASCHEMASSECOND_MICROSECONDSELECTSENSITIVESEPARATORSETSHOWSMALLINTSPATIALSPECIFICSQLSQLEXCEPTIONSQLSTATESQLWARNINGSQL_BIG_RESULTSQL_CALC_FOUND_ROWSSQL_SMALL_RESULTSSLSTARTINGSTRAIGHT_JOINTABLETERMINATEDTHENTINYBLOBTINYINTTINYTEXTTOTRAILINGTRIGGERTRUEUNDOUNIONUNIQUEUNLOCKUNSIGNEDUPDATEUSAGEUSEUSINGUTC_DATEUTC_TIMEUTC_TIMESTAMPVALUESVARBINARYVARCHARVARCHARACTERVARYINGWHENWHEREWHILEWITHWRITEX509XORYEAR_MONTHZEROFILL

0 0