第2章 MySQL 兼容性与差异清单 ​

-- 查看/设置本表的自增分配缓存
-- 不写 AUTO_ID_CACHE 时，TiDB 默认按批次分配 3 万个号（AUTO_ID_CACHE=0 就是「用默认 30000」）
-- 实例 A 缓存 [1,30000]、实例 B 缓存 [30001,60000]，所以重启/切实例就会看到跳号
CREATE TABLE t (id BIGINT PRIMARY KEY AUTO_INCREMENT) AUTO_ID_CACHE = 1;
-- AUTO_ID_CACHE=1：不再本地缓存，每次向集群内的中心分配服务要号（v6.4.0+）
-- → 跨 TiDB 实例也**全局连续递增**，行为最接近 MySQL，代价是高并发插入时多一跳 RPC

SELECT @@tidb_txn_mode;          -- 查看当前默认事务模式，一般 pessimistic（悲观）
SET tidb_txn_mode = 'optimistic'; -- 会话级改为乐观
-- 悲观事务：写冲突时加锁等待，更接近你在 MySQL 里的手感，推荐业务使用
-- 乐观事务：提交时才检测冲突、冲突则整个事务回滚重试，高冲突场景性能差

CREATE TABLE t_2x_id_def (id BIGINT PRIMARY KEY AUTO_INCREMENT, note VARCHAR(20));
CREATE TABLE t_2x_id_c1  (id BIGINT PRIMARY KEY AUTO_INCREMENT, note VARCHAR(20)) AUTO_ID_CACHE = 1;

SHOW CREATE TABLE t_2x_id_def\G   -- 不写就没有任何 AUTO_ID_CACHE 字样（用默认值）
SHOW CREATE TABLE t_2x_id_c1\G    -- 结尾会看到 ... AUTO_ID_CACHE=1

INSERT INTO t_2x_id_def(note) VALUES ('a'),('b'),('c'),('d'),('e');
INSERT INTO t_2x_id_c1 (note) VALUES ('a'),('b'),('c'),('d'),('e');

SELECT id, note FROM t_2x_id_def ORDER BY id;
-- +----+------+
-- | id | note |
-- +----+------+
-- |  1 | a    |
-- |  2 | b    |
-- |  3 | c    |
-- |  4 | d    |
-- |  5 | e    |
-- +----+------+
SELECT MAX(id) FROM t_2x_id_c1;   -- 5，同样连续

INSERT INTO t_2x_id_def(note) VALUES ('f');
SELECT id FROM t_2x_id_def WHERE note = 'f';   -- 6，完全符合 MySQL 直觉
SELECT last_insert_id();                        -- 6（隐式分配可以用它拿回当前会话刚分的号）

INSERT INTO t_2x_id_def(note) VALUES ('g');
INSERT INTO t_2x_id_c1 (note) VALUES ('g');

SELECT id, note FROM t_2x_id_def ORDER BY id;
-- +-------+------+
-- | id    | note |
-- +-------+------+
-- |     1 | a    |
-- |     2 | b    |
-- |     3 | c    |
-- |     4 | d    |
-- |     5 | e    |
-- |     6 | f    |
-- | 30001 | g    |   ← 从 6 直接跳到一个新的批次开头
-- +-------+------+

SELECT id, note FROM t_2x_id_c1 ORDER BY id;
-- 1,2,3,4,5,6,7            ← 不缓存，重启后接着上一行继续

-- 会话 1（连 4000）：
INSERT INTO t_2x_id_def(note) VALUES ('from4000');   -- id = 1
-- 会话 2（连 4001）：
INSERT INTO t_2x_id_def(note) VALUES ('from4001');   -- id = 30001（第二个实例自己拿了下一批）
-- 回到会话 1 再插：id = 2  ← 比刚才那个 30001 还小，所以叫「不全局单调」

SELECT version();     -- 8.0.11-TiDB-v8.5.x → 外键是正式功能，会真拦截
SHOW VARIABLES LIKE 'foreign_key_checks';   -- 默认 ON

CREATE TABLE t_2x_parent (
  id   INT PRIMARY KEY,
  name VARCHAR(20)
);

CREATE TABLE t_2x_child (
  id  INT PRIMARY KEY AUTO_INCREMENT,
  pid INT,
  INDEX idx_pid (pid),                                        -- ← 不要省
  CONSTRAINT fk_child_parent FOREIGN KEY (pid) REFERENCES t_2x_parent(id) ON DELETE CASCADE
);

-- 故意再建一张父表没有索引的，体验 TiDB 的外键限制
CREATE TABLE t_2x_noidx (id INT, name VARCHAR(20));        -- ← 关键：它没有索引
CREATE TABLE t_2x_bad_child (id INT, pid INT, INDEX i(pid),
  CONSTRAINT fk_bad FOREIGN KEY (pid) REFERENCES t_2x_noidx(id));   -- → 报 ERROR 1822

SHOW CREATE TABLE t_2x_child\G
-- Create Table: CREATE TABLE `t_2x_child` (
--   `id` int NOT NULL AUTO_INCREMENT,
--   `pid` int DEFAULT NULL,
--   PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
--   KEY `idx_pid` (`pid`),
--   CONSTRAINT `fk_child_parent` FOREIGN KEY (`pid`) REFERENCES `t_2x_parent` (`id`) ON DELETE CASCADE
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=100001

SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
 WHERE REFERENCED_TABLE_SCHEMA = 'test';
-- fk_child_parent | t_2x_child | pid | t_2x_parent | id

SELECT CONSTRAINT_NAME, DELETE_RULE, UPDATE_RULE
  FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = 'test';
-- fk_child_parent | CASCADE | NO ACTION     ← 没写 ON UPDATE 时默认就是 NO ACTION（行为同 RESTRICT）

INSERT INTO t_2x_child(pid) VALUES (1), (1), (2);        -- 现在 child 共 5 行（第 3 步进了 1 和 2）
SELECT COUNT(*) FROM t_2x_child;                          -- 5
DELETE FROM t_2x_parent WHERE id = 1;                     -- 只删 1 行父数据
SELECT COUNT(*) FROM t_2x_child;                          -- 3   ← 两条 pid=1 的子行被 CASCADE 带删了
SELECT COUNT(*) FROM t_2x_parent;                         -- 1
SELECT ROW_COUNT();                                       -- 可用它确认「服务端只执行了 1 个语句」，子行是数据库自己干的

SET SESSION foreign_key_checks = OFF;
INSERT INTO t_2x_child(pid) VALUES (999);     -- ✅ 成功！孤儿行进去了
SELECT COUNT(*) FROM t_2x_child WHERE pid NOT IN (SELECT id FROM t_2x_parent);
-- 1                                          ← 脏数据现形（注：如果父表已清空要改用 NOT EXISTS）
SET SESSION foreign_key_checks = ON;
INSERT INTO t_2x_child(pid) VALUES (998);     -- ❌ 又报 ERROR 1452

DROP TABLE t_2x_parent;
-- ERROR 3730 (HY000): Cannot drop table 't_2x_parent' referenced by a foreign key constraint 'fk_child_parent' on table 't_2x_child'.
-- 正确做法：先 ALTER TABLE t_2x_child DROP FOREIGN KEY fk_child_parent; 再删表

SELECT @@tidb_txn_mode, @@transaction_isolation, @@innodb_lock_wait_timeout;
-- +-----------------+-----------------------+--------------------------+
-- | pessimistic     | READ-COMMITTED        |                       50 |
-- +-----------------+-----------------------+--------------------------+
-- 三个默认值一起记：TiDB 默认悲观事务 + 默认 RC 隔离 + 锁等待 50 秒

CREATE TABLE t_2x_acct (id INT PRIMARY KEY, bal DECIMAL(10,2) NOT NULL);
INSERT INTO t_2x_acct VALUES (1, 100.00), (2, 50.00);
-- 后面每一轮都拿这两个值做基准：扣 10 加 10，总和永远是 150.00

-- 会话 A（先把它自己的等待上限调小，不然要干等 50 秒）
SET SESSION innodb_lock_wait_timeout = 5;
BEGIN;
UPDATE t_2x_acct SET bal = bal - 10 WHERE id = 1;      -- 立即拿到行锁，Query OK，不提交
-- 先挂在这里，去做会话 B

SET SESSION innodb_lock_wait_timeout = 5;
UPDATE t_2x_acct SET bal = bal - 10 WHERE id = 1;      -- 卡住不动（这就是锁等待，不是死锁）
-- 5 秒后：
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
-- 注：锁等待超时 TiDB 直接复用 MySQL 的错误码 1205（日志/旧文档里偶尔看到同名的内部错误类 8028）
-- 如果你真正碰上是死锁（两事务互等），报错是 1213 Deadlock found when trying to get lock

-- 会话 A：COMMIT;
-- 会话 B：重跑一次 UPDATE，这次会成功
SELECT id, bal FROM t_2x_acct ORDER BY id;
-- +----+--------+
-- | 1  |  80.00 |      ← 被两个事务各扣 10，100 → 80，一行也没丢
-- | 2  |  50.00 |
-- +----+--------+
-- 总和对得上 130.00 = 150.00 - 10 - 10，说明悲观锁保证了串行化效果

-- 会话 A 与 B 都执行：
SET tidb_txn_mode = 'optimistic';
-- 会话 A：BEGIN; UPDATE t_2x_acct SET bal = bal - 10 WHERE id = 1;   ← 秒回，不拿锁
-- 会话 B：BEGIN; UPDATE t_2x_acct SET bal = bal - 10 WHERE id = 1;   ← 也秒回（这就是与悲观最大的体感差异）
-- 会话 A：COMMIT;                                                    ← 成功
-- 会话 B：COMMIT;
ERROR 9007 (HY000): Write conflict, txnStartTS=444970617266225157, conflictStartTS=444970619205738497,
  conflictCommitTS=444970620456845313, key={tableID=85, handle=1}, primary={tableID=85, handle=1}
-- ↑ 字段名比数字重要：txnStartTS 是你的快照，conflict*TS 是对手事务的时间戳，key 告诉你撞的是哪一行
-- （TS 的具体值每次不同，以你实测为准）

SELECT @@tidb_retry_limit, @@tidb_disable_txn_auto_retry;   -- 10 / 1(ON)

-- 会话 A（悲观）：BEGIN; SELECT * FROM t_2x_acct WHERE id=1 FOR UPDATE;   → 拿锁
-- 会话 B（悲观）：SELECT * FROM t_2x_acct WHERE id=1 FOR UPDATE;         → 阻塞等待（跟 MySQL 一样）
-- 会话 B 如果不想等：SELECT * FROM t_2x_acct WHERE id=1 FOR UPDATE NOWAIT;  → 立即报错
-- 但 SKIP LOCKED 在 TiDB 不支持（2.2 清单里那一行），不要指望它做队列

CREATE TABLE t_order (
  id          BIGINT       NOT NULL,           -- 沿用雪花：全局唯一，合并不会撞键
  user_id     BIGINT       NOT NULL,
  order_no    VARCHAR(64)  NOT NULL,
  amount      DECIMAL(10,2) NOT NULL,
  status      TINYINT      NOT NULL DEFAULT 0,
  created     DATETIME     NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_order_no (order_no),           -- ★ 以前靠「分片内唯一」，合表后必须全局唯一
  KEY idx_user (user_id),
  KEY idx_created (created)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;   -- ← 把 collate 显式写回 MySQL 的习惯
-- 高频写的大表可把主键换成 AUTO_RANDOM(5)（第 4 章），但那样就跟原有雪花 id 互斥了，二选一

-- 把各分片库都查一遍，看同一个 id / order_no 是否出现在多张物理表（合并主键必撞）
SELECT id, COUNT(*) c FROM (
  SELECT id FROM `db-orders-0`.t_order_1 UNION ALL SELECT id FROM `db-orders-0`.t_order_2
  UNION ALL SELECT id FROM `db-orders-1`.t_order_1 UNION ALL SELECT id FROM `db-orders-1`.t_order_2
) x GROUP BY id HAVING c > 1;      -- 空集 = 安全；非空 = 先修数据再上 DM
-- 同理把 id 换成 order_no 再跑一遍，因为目标表上有 UNIQUE KEY uk_order_no