-- 嘗試插入重復(fù)的主鍵
INSERT INTO users (id, name) VALUES (1, 'Alice');
INSERT INTO users (id, name) VALUES (1, 'Bob');  -- SQLSTATE[23000]
-- 錯誤信息：Duplicate entry '1' for key 'PRIMARY'

-- 嘗試插入重復(fù)的唯一鍵
CREATE TABLE users (
    email VARCHAR(100) UNIQUE
);
INSERT INTO users (email) VALUES ('test@example.com');
INSERT INTO users (email) VALUES ('test@example.com');  -- SQLSTATE[23000]

-- 父表不存在對應(yīng)記錄
CREATE TABLE departments (
    id INT PRIMARY KEY
);

CREATE TABLE employees (
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(id)
);

INSERT INTO employees (dept_id) VALUES (999);  -- SQLSTATE[23000]
-- 錯誤：Cannot add or update a child row: a foreign key constraint fails

-- 嘗試插入 NULL 到 NOT NULL 字段
CREATE TABLE users (
    name VARCHAR(50) NOT NULL
);
INSERT INTO users (name) VALUES (NULL);  -- SQLSTATE[23000]
-- 錯誤：Column 'name' cannot be null

-- 在應(yīng)用程序中捕獲
try {
    $stmt = $pdo->exec("INSERT ...");
} catch (PDOException $e) {
    echo "SQLSTATE: " . $e->getCode();
    echo "Error message: " . $e->getMessage();
    echo "MySQL error code: " . $e->errorInfo[1];
}

-- 查找是否已存在該鍵值
SELECT * FROM users WHERE id = 1;

-- 查看表的唯一約束
SHOW INDEX FROM users WHERE Non_unique = 0;

-- 查看外鍵約束
SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = DATABASE()
  AND REFERENCED_TABLE_NAME IS NOT NULL;

-- 檢查父表是否存在對應(yīng)記錄
SELECT * FROM parent_table WHERE id = 999;

-- 使用 INSERT IGNORE
INSERT IGNORE INTO users (id, name) 
VALUES (1, 'Alice'), (1, 'Bob'), (2, 'Charlie');
-- 插入 (1,'Bob') 會被忽略，繼續(xù)插入后續(xù)記錄

-- 使用 REPLACE INTO
REPLACE INTO users (id, name) VALUES (1, 'Bob');
-- 先刪除已存在的 id=1 的記錄，再插入新記錄

-- 使用 ON DUPLICATE KEY UPDATE
INSERT INTO users (id, name) VALUES (1, 'Bob')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- 先插入父表
START TRANSACTION;
INSERT INTO departments (id) VALUES (999);
INSERT INTO employees (dept_id) VALUES (999);
COMMIT;

-- 或者使用外鍵級聯(lián)操作
CREATE TABLE employees (
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

// PHP 示例
function safeInsert($pdo, $table, $data) {
    // 檢查唯一鍵沖突
    if (isset($data['id'])) {
        $stmt = $pdo->prepare("SELECT 1 FROM {$table} WHERE id = :id");
        $stmt->execute([':id' => $data['id']]);
        if ($stmt->fetch()) {
            return false; // 已存在
        }
    }
    
    // 執(zhí)行插入
    // ...
}

START TRANSACTION;
BEGIN TRY
    INSERT INTO orders (order_no) VALUES ('ORD001');
    INSERT INTO order_items (order_id, product_id) VALUES (LAST_INSERT_ID(), 1);
    COMMIT;
EXCEPT
    ROLLBACK;
    -- 記錄錯誤
END TRY;

// 重試機制
$maxRetries = 3;
$retryCount = 0;

while ($retryCount < $maxRetries) {
    try {
        $pdo->beginTransaction();
        // 執(zhí)行操作
        $pdo->commit();
        break;
    } catch (PDOException $e) {
        $pdo->rollBack();
        
        if ($e->getCode() == 23000 && $e->errorInfo[1] == 1062) {
            // 重復(fù)鍵錯誤，可能需要生成新ID
            $data['id'] = generateNewId();
            $retryCount++;
            sleep(1);
        } else {
            throw $e;
        }
    }
}

-- 查看所有約束
SELECT 
    CONSTRAINT_SCHEMA,
    CONSTRAINT_NAME,
    TABLE_NAME,
    CONSTRAINT_TYPE
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = DATABASE();

-- 查看表定義
SHOW CREATE TABLE users;

-- 查看列約束
SELECT 
    COLUMN_NAME,
    IS_NULLABLE,
    COLUMN_KEY,
    EXTRA
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'users';