删除记录时提示需要SELECT 权限
(1143, "SELECT command denied to user 'test_delete'@'localhost' for column 'id' in table 'agent_info'")
参考
https://stackoverflow.com/questions/22580529/mysql-duplicate-entry-error-when-trying-to-add-new-column
修改表增加行时提示主键有重复记录
其实没有，是因为5.6开始支持在线修改表结构。向新表追加dml log时会出现空洞重复
参考
https://bugs.mysql.com/bug.php?id=76895
WHERE columnX=NULL是否规范？
不规范
应使用 WHERE columnX IS NULL
MySQL中WHERE columnX=NULL等效WHERE columnX IS NULL
SQL92规范中中WHERE columnX=NULL永远不成立
参考
https://stackoverflow.com/questions/2747279/something-about-ansi-nulls
You can't specify target table for update in FROM clause
参考
https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause
获取准确的页面记录数量总计
SELECT s.*,t.count
FROM
instance_summary AS s
JOIN
(SELECT COUNT(*) AS `count` FROM instance_summary) AS t
```
### INSERT ON DUPLICATE KEY UPDATE 死锁
> 在5.7版本上比较常见
> 解决办法: 先更新,后插入
```java
int affectRows = update(sql)
if (affectRows ==0 )
{
// 某些情况下可能要先检查行记录是否存在,然后insert。通过主动采样的方式减小异常发生
insert(sql)
}
参考
The following notes will be helpful for you,
mysql_affected_rows() returns
+0: a row wasn't updated or inserted (likely because the row already existed, but no field values were actually changed during the UPDATE).
+1: a row was inserted
+2: a row was updated
-1: in case of error.
https://stackoverflow.com/questions/9397291/mysql-affected-rows-returns-0-for-update-statement-even-when-an-update-actuall
