Skillversitas

MySQL Compatibility

TiDB is highly compatible with the MySQL 5.7 and 8.0 protocols. Most MySQL applications can run on TiDB with no code changes.

What Is Compatible

  • The MySQL connection protocol
  • MySQL clients (mysql, MySQL Workbench, Navicat, DBeaver, etc.)
  • The majority of SQL syntax: SELECT, INSERT, UPDATE, DELETE, JOIN, subqueries
  • Core data types: INT, VARCHAR, TEXT, DATE, TIMESTAMP, DECIMAL, JSON
  • MySQL functions: string, numerical, date/time, aggregation
  • Indexes: B-Tree, unique index, composite index
  • Transactions: START TRANSACTION, COMMIT, ROLLBACK
  • User management: CREATE USER, GRANT, REVOKE
  • Views (read-only)
  • Table partitioning: RANGE, HASH, LIST, KEY

What Is Not Supported

  • Stored procedures and functions
  • Triggers
  • Events
  • User-defined functions
  • FULLTEXT indexes (the syntax can be parsed but the index is not active)
  • SPATIAL/GIS functions and indexes
  • XA transactions
  • CHECK TABLE
  • HANDLER statements
  • Descending indexes

Key Differences from MySQL

AUTO_INCREMENT

-- MySQL: auto-increment IDs are always sequential per node
-- TiDB: auto-increment IDs are globally unique but not always sequential

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
);

-- To reduce hotspots, use AUTO_RANDOM
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_RANDOM,
    amount DECIMAL(10,2)
);

Default Character Set

-- TiDB default: utf8mb4
-- MySQL 5.7 default: latin1
-- MySQL 8.0 default: utf8mb4

SHOW VARIABLES LIKE 'character_set_server';
-- TiDB: utf8mb4

DDL Operations

All DDL in TiDB runs online (no table locks). However, there are some limitations:

  • You cannot modify multiple columns at once with ALTER TABLE ... ADD COLUMN, DROP COLUMN
  • You cannot re-add an AUTO_INCREMENT that has been removed
  • Not all data type changes are supported

Views

-- MySQL: views can be UPDATED, INSERTed, and DELETEd
-- TiDB: views are read-only

CREATE VIEW active_users AS
SELECT * FROM users WHERE status = 'active';

-- This does NOT work in TiDB:
UPDATE active_users SET name = 'test';

Transaction Isolation

By default, TiDB uses REPEATABLE READ (snapshot isolation). TiDB also supports READ COMMITTED.

-- Set the isolation level
SET SESSION transaction_isolation = 'READ-COMMITTED';

Compatible Tools

The following MySQL tools work with TiDB:

  • MySQL CLI & Workbench
  • Navicat
  • DBeaver
  • PHPMyAdmin
  • SQLAlchemy (Python)
  • JDBC (Java)
  • Go SQL Driver
  • Node.js mysql2

In the next chapter, we will learn how to create a database in TiDB.