sql

Mysql關鍵字和保留字(注意)


SQLGrammarException:

頭部錯誤資訊:

2019-09-24 14:51:11.842  INFO 6728 --- [nio-9002-exec-2] o.s.web.servlet.DispatcherServlet        : FrameworkServlet 'dispatcherServlet': initialization completed in 18 ms
Hibernate: select recruit0_.id as id1_1_0_, recruit0_.address as address2_1_0_, recruit0_.condition as conditio3_1_0_, recruit0_.content1 as content4_1_0_, recruit0_.content2 as content5_1_0_, recruit0_.createtime as createti6_1_0_, recruit0_.education as educatio7_1_0_, recruit0_.eid as eid8_1_0_, recruit0_.jobname as jobname9_1_0_, recruit0_.label as label10_1_0_, recruit0_.salary as salary11_1_0_, recruit0_.state as state12_1_0_, recruit0_.type as type13_1_0_, recruit0_.url as url14_1_0_ from tb_recruit recruit0_ where recruit0_.id=?
Hibernate: insert into tb_recruit (address, condition, content1, content2, createtime, education, eid, jobname, label, salary, state, type, url, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2019-09-24 14:51:11.939  WARN 6728 --- [nio-9002-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   :  Error: 1064, SQLState: 42000
2019-09-24 14:51:11.939 ERROR 6728 --- [nio-9002-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : You have an error in your  syntax; check the manual that corresponds to your  server version for the right syntax to use near 'condition, content1, content2, createtime, education, eid, jobname, label, salar' at line 1
2019-09-24 14:51:11.939  INFO 6728 --- [nio-9002-exec-2] o.h.e.j.b.internal.AbstractBatchImpl     : HHH000010: On release of batch it still contained JDBC statements
2019-09-24 14:51:11.943 ERROR 6728 --- [nio-9002-exec-2] o.h.i.ExceptionMapperStandardImpl        : HHH000346: Error during managed flush [org.hibernate.exception.SQLGrammarException: could not execute statement]
org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.:242)

。。。。。。。中間的省略

	at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:68)
	at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:536)
	... 72 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'condition, content1, content2, createtime, education, eid, jobname, label, salar' at line 1
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 ‘condition, content1,

condition mysql5.7的保留字,因資料庫某表中一欄位用的condition,結果在用spring-boot-jpa的時候新增記錄,一直失敗。

又如: Desc,mysql保留的關鍵字,在建立欄位時給自己提個醒!!!

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 
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, TestWebUrl, UpdateTime, WebUrl) values (null, 'ajaxurl', 'baby-techcenter-' at line 1

關鍵字VS保留字

SQL和程式語言一樣,是有關鍵字的。但是我們所理解的關鍵字在SQL中分為兩類:

關鍵字

這類字在Mysql中具有特殊含義,例如常見的alter、static、cache。雖然具有特殊含義,但是還是可以作為Mysql中的識別符號來使用的。例如你建立一個表名為static的表並沒有什麼問題,但是在實際中不推薦這麼做。

保留字

這類字在Mysql中就比較強勢了。保留字,顧名思義,就是Mysql自身保留的識別符號。一般情況下是不允許使用的,例如select、insert等。但是有兩種例外的情況:

加反引號,即可當做正常的識別符號使用
接在另一個合法的識別符號後面,例如你建立一個mydb.insert表,就是沒有問題的。
不過話說回來,誰會這麼做呢?

Mysql命名建議

用英文,如果不知道怎麼表述,就去google,千萬不要用漢語拼音將就;
不要使用關鍵字、更不要使用保留字;
儘量使用統一的字首。

參考:https://dev.mysql.com/doc/refman/5.7/en/keywords.html
參考:https://blog.csdn.net/hfut_wowo/article/details/81011952

本文章已修改原文用詞符合繁體字使用者習慣使其容易閱讀

版權宣告:此處為CSDN博主「YRYY」的原創文章,依據CC 4.0 BY-SA版權協議,轉載請附上原文出處連結及本宣告。

原文連結:https://blog.csdn.net/u012847056/article/details/101285539