01.TiDB与MySQL语法

0、TiDB基础

1）Mac安装TiDB

docker pull pingcap/tidb:latest
docker run -d --name tidb-server -p 4000:4000 -p 10080:10080 pingcap/tidb:latest 
mysql -h 127.0.0.1 -P 4000 -u root

2）与MySQL对比

• 外键约束：TiDB 不支持外键约束
• 分区表：TiDB 对分区表的支持与 MySQL 存在差异
• 自增列：TiDB 中的自增列值可能不连续
• 存储过程和触发器：TiDB 不支持这些功能
• DDL 操作：TiDB 的 DDL 操作是异步执行的，避免了锁表
• 分布式查询的性能优化：由于 TiDB 是分布式数据库，某些语法在大规模数据集上的执行性能可能不同，需要进一步优化

1、创建表和库一致

• 创建数据库 完全一样

CREATE DATABASE IF NOT EXISTS test_db;

• 创建表（基本语法一致）
  • 差异1：TiDB中AUTO_INCREMENT 自增列的值在大规模并发下可能不连续
  • 差异2：不支持 FOREIGN KEY 外键约束

CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2、增删改查完全一样

-- 插入数据
INSERT INTO users (name, age) VALUES ('Alice', 30);

-- 更新数据
UPDATE users SET age = 31 WHERE name = 'Alice';

-- 删除数据
DELETE FROM users WHERE name = 'Alice';

-- 查询数据
SELECT * FROM users WHERE age > 25;

3、连表查询语法一致

• 分布式执行计划优化
  • 虽然语法相同，但由于 TiDB 是分布式数据库，TiDB 对 JOIN 的执行可能不如 MySQL 效率高，尤其是在大型数据集上
  • 需要通过 EXPLAIN 来查看执行计划，优化 JOIN 的分布式执行

SELECT u.name, o.id
FROM users u
JOIN orders o ON u.id = o.user_id;

4、事务

• 基本的事务操作在 TiDB 和 MySQL 中是一样的

• 悲观事务与乐观事务差异
  • TiDB 默认使用乐观事务，而 MySQL 默认使用悲观事务
  • TiDB 支持通过配置将事务模式改为悲观事务
  • 乐观事务适用于高并发下的性能优化，但可能导致事务冲突
  • 需要根据业务场景选择事务模式

START TRANSACTION;
UPDATE users SET age = 32 WHERE name = 'Bob';
COMMIT;

5、其他查询

# WHERE 语句完全一致
SELECT * FROM users WHERE age > 30;

# LIKE 语句完全一致
SELECT * FROM users WHERE name LIKE 'A%';

# ORDER BY 语句 完全一致
SELECT * FROM users ORDER BY age DESC;

# GROUP BY 语句 完全一致
SELECT age, COUNT(*) FROM users GROUP BY age;

# HAVING 语句 完全一致
SELECT age, COUNT(*) FROM users GROUP BY age HAVING COUNT(*) > 1;

# LIMIT 和 OFFSET 完全一致
SELECT * FROM users ORDER BY created_at DESC LIMIT 10 OFFSET 20;

# UNION 和 UNION ALL 完全一致
SELECT name FROM users UNION SELECT name FROM customers;

# 子查询 (Subquery) 完全一致
SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users);

# DROP 语句 完全一致
DROP TABLE users;
DROP DATABASE test_db;

• ALTER TABLE 存在差异
  • 分区表限制：TiDB 的分区表支持与 MySQL 存在差异，某些 ALTER TABLE 操作可能会受到分区表设计的影响
  • 执行方式：TiDB 的 ALTER TABLE 操作是异步的，因此不会像 MySQL 那样锁表

ALTER TABLE users ADD COLUMN email VARCHAR(255);