## 模块 1：MySQL 兼容性 ![demo_203-tidb-x-mysql-compat.jpg 85%](https://lab-static.pingcap.com/images/2025/11/27/6302b6914af7014616941c0c3feec84cd3a8d3d1.jpg) 在本模块中，我们将连接到 TiDB，创建表结构，导入数据，并体验 MySQL 8.x 的特性，例如窗口函数，同时也了解 TiDB 对某些不支持特性（如存储过程）的表现。更全面的兼容性列表请参考：[https://docs.pingcap.com/tidb/stable/mysql-compatibility/](https://docs.pingcap.com/tidb/stable/mysql-compatibility/)。 ### 步骤 1. 登录实验 Ubuntu 虚拟机 - 点击 [终端 1](ssh://monitor1-ssh#1) 打开一个新的终端窗口，连接到本次实验分配到的 Ubuntu 虚拟机。你将在这个终端中访问 TiDB Cloud Starter 集群。 ### 步骤 2. 检查至 TiDB Cloud 集群的连通性 - 你可以用和连接标准 MySQL 数据库一样的方式连接 TiDB。接下来将通过三种方式尝试连接 TiDB Cloud Starter 集群。（若需退出 mysql 客户端，请输入 `EXIT`。） - **A. SSL 加密连接：** 使用如下命令连接。 ```bash mysql \ --comments -u '${LAB:SERVERLESS_CLUSTER_USERNAME}' -p'${LAB:SERVERLESS_CLUSTER_PASSWORD}' \ -h ${LAB:SERVERLESS_CLUSTER_HOST} -P ${LAB:SERVERLESS_CLUSTER_PORT} \ --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/ssl/certs/ca-certificates.crt ``` ```output mysql> ``` ```sql EXIT; ``` - **B. SSL 加密连接（省略 --ssl-mode）：** 如果不指定 `--ssl-mode` 参数，默认模式为 `PREFERRED`，意味着 MySQL 客户端会优先尝试建立加密连接，如果失败则降级为明文连接。 ```bash mysql \ --comments -u '${LAB:SERVERLESS_CLUSTER_USERNAME}' -p'${LAB:SERVERLESS_CLUSTER_PASSWORD}' \ -h ${LAB:SERVERLESS_CLUSTER_HOST} -P ${LAB:SERVERLESS_CLUSTER_PORT} ``` ```output mysql> ``` ```sql EXIT; ``` - **C. 明文连接（不带 SSL）：** 指定 `--ssl-mode=DISABLED`，会出现连接错误。 ```bash mysql \ --comments -u '${LAB:SERVERLESS_CLUSTER_USERNAME}' -p'${LAB:SERVERLESS_CLUSTER_PASSWORD}' \ -h ${LAB:SERVERLESS_CLUSTER_HOST} -P ${LAB:SERVERLESS_CLUSTER_PORT} \ --ssl-mode=DISABLED ``` ```output mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1105 (HY000): Connections using insecure transport are prohibited. See https://docs.tidb.io/tidbcloud/secure-connections-to-serverless-tier-clusters ``` > [!NOTE] > 本实验采用公网连接方式，实际应用中更常用、更推荐 TiDB Cloud Starter 的私网访问链路。 - 登录 TiDB Cloud 控制台的 [Clusters 页面](https://tidbcloud.com/project/clusters)，点击你本次实验创建的集群名称。如果你是通过“创建新集群”开始实验的，集群名称为 `${LAB:SERVERLESS_CLUSTER_NAME}`。 ![choose-starter-cluster-v2.jpg 70%](https://lab-static.pingcap.com/images/2025/11/24/968052d742cea1bce34cda07961dbffb8d309ebd.jpg) - 点击 `Connect`（连接）。 ![starter-connect.jpg 80%](https://lab-static.pingcap.com/images/2025/11/18/dfb8f70aacdf6b738e74976a2d292f67a5417314.jpg) - 在 `Connection Type`（连接类型）下可见 `Private Endpoint`（私有端点）。 ![starter-connect-methods.jpg 70%](https://lab-static.pingcap.com/images/2025/11/18/724569d619c6850877608947c662edff50462206.jpg) - 实验结束后，可在此页面开启私有端点功能，实现通过企业内网（如 VPC）访问集群。当前可保持默认，继续至下一步。 ### 步骤 3. 检查基本功能 - 在 [终端 1](ssh://monitor1-ssh#1) 连接到 TiDB。 ```bash mysql \ --comments -u '${LAB:SERVERLESS_CLUSTER_USERNAME}' -p'${LAB:SERVERLESS_CLUSTER_PASSWORD}' \ -h ${LAB:SERVERLESS_CLUSTER_HOST} -P ${LAB:SERVERLESS_CLUSTER_PORT} \ --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/ssl/certs/ca-certificates.crt ``` ```output mysql> ``` - 创建名为 `${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}` 的新数据库。TiDB 的用法与 MySQL 完全一致。 ```sql DROP DATABASE IF EXISTS ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}; CREATE DATABASE ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}; SHOW DATABASES\G ``` ```output *************************** 1. row *************************** Database: INFORMATION_SCHEMA *************************** 2. row *************************** Database: PERFORMANCE_SCHEMA *************************** 3. row *************************** Database: ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME} *************************** 4. row *************************** Database: mysql *************************** 5. row *************************** Database: test 5 rows in set (0.02 sec) ``` - 创建一张简单的数据表。TiDB 也与 MySQL 操作方法相同。 ```sql USE ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}; CREATE TABLE color (id INT AUTO_INCREMENT, name TEXT, Point INT, PRIMARY KEY (id)); INSERT INTO color (name, Point) VALUES ('red', 1); INSERT INTO color (name, Point) VALUES ('orange', 1); INSERT INTO color (name, Point) VALUES ('yellow', 2); SELECT * FROM color; ``` ```output Database changed Query OK, 0 rows affected (0.14 sec) Query OK, 1 row affected (0.04 sec) Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.01 sec) +----+--------+-------+ | id | name | Point | +----+--------+-------+ | 1 | red | 1 | | 2 | orange | 1 | | 3 | yellow | 2 | +----+--------+-------+ 3 rows in set (0.01 sec) ``` - 查看表的默认字符集和排序规则。 ```sql SHOW CREATE TABLE color\G ``` ```output *************************** 1. row *************************** Table: color Create Table: CREATE TABLE `color` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` text DEFAULT NULL, `Point` int(11) DEFAULT NULL, PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30001 1 row in set (0.00 sec) ``` - 窗口函数示例。 ```sql SELECT id, name, Point, RANK() OVER w as 'rank' FROM color WINDOW w AS (ORDER BY Point); ``` ```output +----+--------+-------+------+ | id | name | Point | rank | +----+--------+-------+------+ | 1 | red | 1 | 1 | | 2 | orange | 1 | 1 | | 3 | yellow | 2 | 3 | +----+--------+-------+------+ 3 rows in set (0.01 sec) ``` - TiDB 对 MySQL 兼容性很强，但某些 MySQL 特性（如存储过程）暂时不支持。尝试如下操作将失败： ```sql delimiter // CREATE PROCEDURE sample() BEGIN SELECT * FROM color; END // delimiter ; ``` ```output ERROR 8108 (HY000): Unsupported type *ast.ProcedureInfo ``` - 通过上述操作，你已大致了解如何连接和使用 TiDB，下一步我们将构建一个更完整的示例业务库。 ### 步骤 4. 创建 Bookshop 示例表 - 在 [终端 1](ssh://monitor1-ssh#1) 执行如下命令，快速查看实验 SQL 脚本内容（该脚本会为样例书店应用创建 6 个表并写入部分数据）： ```sql SYSTEM cat create_bookshop_schema_in_current_db.sql | egrep '(CREATE TABLE|INSERT INTO)'; ``` ```output CREATE TABLE `authors` ( INSERT INTO `authors` CREATE TABLE `book_authors` ( INSERT INTO `book_authors` CREATE TABLE `books` ( INSERT INTO `books` CREATE TABLE `orders` ( CREATE TABLE `ratings` ( INSERT INTO `ratings` CREATE TABLE `users` ( INSERT INTO `users` ``` - 执行脚本，将样例数据导入 `${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}` 库。 ```sql USE ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}; SOURCE create_bookshop_schema_in_current_db.sql ``` ```output Query OK, 0 rows affected (0.01 sec) ... Query OK, 0 rows affected (0.01 sec) ``` ### 步骤 5. 查询样例数据 - 下面我们进行一个综合查询，分析各大类图书的评分前 3 名，并统计对应库存。虽然下面的 SQL 较长，但它主要旨在演示 TiDB 对 JOIN、GROUP BY、ORDER BY、聚合函数、窗口函数、子查询等高级语法的原生支持。 ```sql SELECT b.type, COUNT(b.id) AS book_count, AVG(r.score) avg_score, v.top_3_rated_title FROM books b JOIN ratings r ON b.id = r.book_id JOIN (SELECT ov.type, GROUP_CONCAT(ov.title) top_3_rated_title FROM (SELECT iv.type, iv.title, RANK() OVER (PARTITION BY iv.type ORDER BY book_avg_score DESC) rank_in_type FROM (SELECT books.type, books.title, AVG(ratings.score) as book_avg_score FROM books as books JOIN ratings as ratings ON books.id = ratings.book_id GROUP BY books.type, books.title) iv ) ov WHERE rank_in_type < 4 GROUP BY ov.type ) v ON v.type = b.type GROUP BY type, top_3_rated_title ORDER BY avg_score DESC; ``` ```output +------------------------------+------------+-----------+------------------------------------------------------------------------------------+ | type | book_count | avg_score | top_3_rated_title | +------------------------------+------------+-----------+------------------------------------------------------------------------------------+ | Comics | 64 | 3.5156 | Mrs Robinson,Be My Baby,On the Atchison | | Arts | 86 | 3.4535 | Give Me Everything,Georgia On My Mind,Besame Mucho | | Magazine | 59 | 3.3051 | Wishing Well,Stayin' Alive,Spanish Harlem | | Kids | 48 | 3.1875 | What Goes Around Comes Around,Gonna Make You Sweat (Everybody Dance Now),Hey There | | Life | 175 | 3.1657 | Play That Funky Music,Open Arms,Every Breath You Take | | Sports | 81 | 3.1605 | Waterfalls,The Hustle,My Sweet Lord | | Education & Reference | 65 | 3.1231 | Greatest Love of All,Addicted to Love,ABC | | Science & Technology | 149 | 3.0671 | Build Me Up Buttercup,Dilemma,Daydream Believer | | Humanities & Social Sciences | 78 | 2.9615 | Higher Love,12th Street Rag,Body & Soul | | Novel | 44 | 2.9318 | The Power of Love,Thrift Shop,Summer in the City | +------------------------------+------------+-----------+------------------------------------------------------------------------------------+ 10 rows in set (0.01 sec) ``` - 无需花太多精力深入分析查询内容，直接进入下一步即可。 ### 步骤 6. 使用 Python 脚本查询样例数据 - 退出 mysql 客户端，回到操作系统命令行。 ```sql EXIT; ``` - 创建 Python 虚拟环境。 ```bash python3 -m venv ~/.venv ~/.venv/bin/pip install mysql-connector-python prettytable dotenv ``` - 创建名为 `top3books.py` 的脚本，用于执行上述分析类 SQL 查询。该脚本采用 `mysql.connector` 模块连接 TiDB（与 MySQL 相同）。 ```bash cat > top3books.py <<'EOF' import mysql.connector import datetime from prettytable import PrettyTable config = { 'user': '${LAB:SERVERLESS_CLUSTER_USERNAME}', 'password': '${LAB:SERVERLESS_CLUSTER_PASSWORD}', 'host': '${LAB:SERVERLESS_CLUSTER_HOST}', 'database': '${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}', 'raise_on_warnings': False, 'port': ${LAB:SERVERLESS_CLUSTER_PORT}, } def execQuery(query, queryName): print(queryName) print("Query execution start time: ", datetime.datetime.now().strftime("%H:%M:%S.%f")) # 执行查询 cursor.execute(query) print("Query execution end time: ", datetime.datetime.now().strftime("%H:%M:%S.%f")) # 获取所有结果 results = cursor.fetchall() # 创建 PrettyTable 对象 my_table = PrettyTable() # 添加表头 field_names = [i[0] for i in cursor.description] my_table.field_names = field_names # 添加数据行 for row in results: my_table.add_row(row) # 打印表格 print(my_table) print("==========================") try: # 建立连接 conn = mysql.connector.connect(**config) # 创建游标 cursor = conn.cursor() # SQL 查询语句 queryName= "■■■查询：每个类别评分前三的图书■■■" query = """ SELECT b.type, COUNT(b.id) AS book_count, AVG(r.score) avg_score, v.top_3_rated_title FROM books b JOIN ratings r ON b.id = r.book_id JOIN (SELECT ov.type, GROUP_CONCAT(ov.title) top_3_rated_title FROM (SELECT iv.type, iv.title, RANK() OVER (PARTITION BY iv.type ORDER BY book_avg_score DESC) rank_in_type FROM (SELECT books.type, books.title, AVG(ratings.score) as book_avg_score FROM books as books JOIN ratings as ratings ON books.id = ratings.book_id GROUP BY books.type, books.title) iv ) ov WHERE rank_in_type < 4 GROUP BY ov.type ) v ON v.type = b.type GROUP BY type, top_3_rated_title ORDER BY avg_score DESC; """ execQuery(query, queryName) except mysql.connector.Error as err: print(f"Error: {err}") finally: # 关闭连接 if conn.is_connected(): cursor.close() conn.close() print("MySQL 连接已关闭") EOF ls -l top3books.py ``` ```output -rw-r--r--. 1 ubuntu ubuntu 3636 Apr 28 02:22 top3books.py ``` - 运行脚本，获得分析报告。 ```bash ~/.venv/bin/python top3books.py ``` ```output ■■■查询：每个类别评分前三的图书■■■ Query execution start time: 14:05:18.613932 Query execution end time: 14:05:18.629749 +------------------------------+------------+-----------+------------------------------------------------------------------------------------+ | type | book_count | avg_score | top_3_rated_title | +------------------------------+------------+-----------+------------------------------------------------------------------------------------+ | Comics | 64 | 3.5156 | Mrs Robinson,On the Atchison,Be My Baby | | Arts | 86 | 3.4535 | Give Me Everything,Georgia On My Mind,Besame Mucho | | Magazine | 59 | 3.3051 | Wishing Well,Stayin' Alive,Spanish Harlem | | Kids | 48 | 3.1875 | What Goes Around Comes Around,Gonna Make You Sweat (Everybody Dance Now),Hey There | | Life | 175 | 3.1657 | Play That Funky Music,Open Arms,Every Breath You Take | | Sports | 81 | 3.1605 | Waterfalls,The Hustle,My Sweet Lord | | Education & Reference | 65 | 3.1231 | Greatest Love of All,Addicted to Love,ABC | | Science & Technology | 149 | 3.0671 | Build Me Up Buttercup,Dilemma,Daydream Believer | | Humanities & Social Sciences | 78 | 2.9615 | Higher Love,12th Street Rag,Body & Soul | | Novel | 44 | 2.9318 | The Power of Love,Thrift Shop,Summer in the City | +------------------------------+------------+-----------+------------------------------------------------------------------------------------+ ========================== MySQL 连接已关闭 ``` - 在本模块中，我们通过 SQL 和 Python 以与 MySQL 完全相同的方式访问和使用了 TiDB，除了某些细微兼容性差异。如果需要访问 TiDB 的其他驱动或 ORM 方式，请参考[官方文档](https://docs.pingcap.com/tidbcloud/dev-guide-choose-driver-or-orm/)或浏览我们的[相关实验](/labs?target=Developer)。现在可以前往下一个模块。