您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 常見錯誤代碼完整對照表（1005-1235）與 Java 異常轉譯工具類

編輯：MySQL 資料庫相關知識

高頻錯誤代碼速查表（節錄 1005-1235）

代碼	SQLSTATE	訊息	典型原因與對策
1005	HY000	Can't create table	外鍵定義錯誤、磁碟滿、權限不足 → 檢查 FK 語法、磁碟空間、目錄權限
1007	HY000	Database exists	CREATE DATABASE 未加 IF NOT EXISTS → 加上判斷或捕獲忽略
1045	28000	Access denied	帳號/密碼錯誤、Host 不匹配、權限表損壞 → 核對連線資訊、重置密碼
1062	23000	Duplicate entry	主鍵/唯一鍵衝突 → 業務層去重、INSERT IGNORE/ON DUPLICATE KEY UPDATE
1146	42S02	Table doesn't exist	拼寫錯誤、大小寫敏感、Schema 錯誤 → 檢查 lower_case_table_names
1158	08S01	Network read error	網路斷線、MTU 過大、防火牆 → 檢查網路、調整 net_read_timeout
1205	HY000	Lock wait timeout	長事務、索引缺失、死鎖 → 優化 SQL、縮短事務、檢查 innodb_lock_wait_timeout
1213	40001	Deadlock found	並發更新同一記錄、不同順序鎖表 → 統一加鎖順序、降低隔離級別
1216	23000	Foreign key constraint fails (child)	子表插入父表不存在的 FK → 先插入父表、或暫時關閉 FK 檢查
1217	23000	Foreign key constraint fails (parent)	刪除/更新被引用的父表記錄 → 先處理子表、或 CASCADE
2006	HY000	MySQL server has gone away	封包過大、wait_timeout 觸發、服務重啟 → 調整 max_allowed_packet、連線池驗證查詢
2013	HY000	Lost connection during query	查詢過慢觸發 timeout、網路抖動 → 優化慢查詢、調整 net_read/write_timeout

Java 整合：MySQLErrorCode 枚舉 + 快速查找

public enum MySQLErrorCode {
  ER_CANT_CREATE_TABLE(1005, "Can't create table"),
  ER_DB_CREATE_EXISTS(1007, "Database exists"),
  ER_DUP_ENTRY(1062, "Duplicate entry"),
  ER_NO_SUCH_TABLE(1146, "Table doesn't exist"),
  ER_NET_READ_ERROR(1158, "Network read error"),
  ER_LOCK_WAIT_TIMEOUT(1205, "Lock wait timeout"),
  ER_DEADLOCK(1213, "Deadlock found"),
  ER_NO_REFERENCED_ROW(1216, "Foreign key constraint fails (child)"),
  ER_ROW_IS_REFERENCED(1217, "Foreign key constraint fails (parent)"),
  ER_SERVER_GONE(2006, "MySQL server has gone away"),
  ER_LOST_CONNECTION(2013, "Lost connection during query");

  private final int code;
  private final String msg;
  MySQLErrorCode(int code, String msg) { this.code = code; this.msg = msg; }
  public int getCode() { return code; }
  public String getMsg() { return msg; }

  public static String getMsg(int code) {
    for (MySQLErrorCode e : values()) if (e.code == code) return e.msg;
    return "Unknown MySQL error code: " + code;
  }
}

Spring Boot 異常轉譯範例

@Repository
public class MySQLExceptionTranslator extends SQLErrorCodeSQLExceptionTranslator {
  @Override
  protected String translate(String task, String sql, SQLException ex) {
    String vendorMsg = MySQLErrorCode.getMsg(ex.getErrorCode());
    return String.format("[MySQL-%d] %s | Task: %s | SQL: %s", ex.getErrorCode(), vendorMsg, task, sql);
  }
}

// 配置
@Bean
public DataSource dataSource() {
  HikariDataSource ds = new HikariDataSource();
  ds.setConnectionInitSql("SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'");
  return ds;
}

@Bean
public JdbcTemplate jdbcTemplate(DataSource ds) {
  JdbcTemplate t = new JdbcTemplate(ds);
  t.setExceptionTranslator(new MySQLExceptionTranslator());
  return t;
}