Repository: ariga/atlas
Pull request #587 (2022-02-15 15:19:34)

sql/mysql: TiDB inspector for MySQL driver
In this PR I begin the work of integrating TiDB with Atlas.  
I began with solving the issues of the first integration test (`TestMySQL_AddDropTable`) which are well described [here](https://github.com/pingcap/tidb/issues/32411) and [here](https://github.com/pingcap/tidb/issues/28918).  
Some other tests were also solved.  
in `tidb.go` I implement a decorator for `mysql` inspector which applies patches after MySQL inspect. 



Patch:
diff --git a/doc/md/ddl/sql.md b/doc/md/ddl/sql.md
deleted file mode 100644
index 628423f2..00000000
--- a/doc/md/ddl/sql.md
+++ /dev/null
@@ -1,197 +0,0 @@
----
-id: ddl-sql
-title: SQL Syntax
-slug: /ddl/sql
----
-
-The [sqlspec](https://pkg.go.dev/ariga.io/atlas@master/sql/sqlspec) package defines
-the resource types used to describe an SQL database schema. Used with the
-[Atlas HCL](intro.md#hcl) syntax, it is easy to compose documents describing the desired
-state of a SQL database.  
-
-## Resource Types
-
-### Schema
-
-```hcl
-# Empty schema.
-schema "public" {}
-
-# Schema with attributes.
-schema "main" {
-  charset   = "utf8mb4"
-  collation = "utf8mb4_0900_ai_ci"
-}
-```
-
-### Table
-
-A `table` describes a table in a SQL database.
-
-```hcl
-table "users" {
-  schema = schema.public
-  column "id" {
-    type = int
-  }
-  column "name" {
-    type = varchar(255)
-  }
-  column "manager_id" {
-    type = int
-  }
-  primary_key {
-    columns = [
-      column.id
-    ]
-  }
-  index "idx_name" {
-    columns = [
-      column.name
-    ]
-    unique = true
-  }
-  foreign_key "manager_fk" {
-    columns = [column.manager_id]
-    ref_columns = [column.id]
-    on_delete = "CASCADE"
-    on_update = "NO ACTION"
-  }
-}
-```
-
-#### Properties
-
-| Name        | Kind            | Type        | Description                                  |
-|-------------|-----------------|-------------|----------------------------------------------|
-| schema      | attribute       | reference   | References the  schema containing the table. |
-| column      | resource (list) | column      | Describes a column in the table.             |
-| primary_key | resource        | primary_key | Describes the table's primary key.           |
-| foreign_key | resource (list) | foreign_key | Describes the table's foreign keys.          |
-| index       | resource (list) | index       | Describes the table's indexes.               |
-
-### Column
-
-A column is a child resource of a `table`. 
-
-#### Examples
-
-```hcl
-column "name" {
-  type = text
-  null = false
-}
-
-column "age" {
-  type = integer
-  default = 42
-}
-
-column "active" {
-  type = tinyint(1)
-  default = true
-}
-```
-
-#### Properties
-
-| Name    | Kind      | Type                     | Description                                                |
-|---------|-----------|--------------------------|------------------------------------------------------------|
-| null    | attribute | bool                     | Defines whether the column is nullable.                    |
-| type    | attribute | *schemaspec.Type         | Defines the type of data that can be stored in the column. |
-| default | attribute | *schemaspec.LiteralValue | Defines the default value of the column.                   |
-
-### Column Types
-
-The SQL dialects supported by Atlas (Postgres, MySQL, MariaDB, and SQLite) vary in
-the types they support. At this point, the Atlas DDL does not attempt to abstract 
-away the differences between various databases. This means that the schema documents
-are tied to a specific database engine and version. This may change in a future version
-of Atlas as we plan to add "Virtual Types" support. This section lists the various
-types that are supported in each database.
-
-For a full list of supported column types, [click here](sql_types.md).
-
-### Primary Key
-
-A `primary_key` is a child resource of a `table`, and it defines the table's
-primary key. 
-
-#### Example 
-
-```hcl
-primary_key {
-  columns = [column.id]
-}
-```
-
-#### Properties
-
-| Name    | Kind      | Type                     | Description                                                    |
-|---------|-----------|--------------------------|----------------------------------------------------------------|
-| columns | resource  | reference (list)         | A list of references to columns that comprise the primary key. |
-
-### Foreign Key
-
-Foreign keys are child resources of a `table`, and it defines some columns in the table
-as references to columns in other tables. 
-
-#### Example
-
-```hcl
-foreign_key "manager_fk" {
-  columns = [column.manager_id]
-  ref_columns = [column.id]
-  on_delete = "CASCADE"
-  on_update = "NO ACTION"
-}
-```
-
-#### Properties
-
-| Name        | Kind      | Type                   | Description                               |
-|-------------|-----------|------------------------|-------------------------------------------|
-| columns     | attribute | reference (list)       | The columns that reference other columns. |
-| ref_columns | attribute | reference (list)       | The referenced columns.                   |
-| on_update   | attribute | schema.ReferenceOption | Defines what to do on update.             |
-| on_delete   | attribute | schema.ReferenceOption | Defines what to do on delete.             |
-
-### Index
-
-Indexes are child resources of a `table`, and it defines an index on the table.
-
-#### Example
-
-```hcl
-index "idx_name" {
-    columns = [
-      column.name
-    ]
-    unique = true
-}
-
-index "idx_name" {
-    on {
-        column = column.rank
-    }
-    on {
-        column = column.score
-        desc = true
-    }
-    unique = true
-}
-
-index "idx_name" {
-  type = GIN
-  columns = [column.data]
-}
-```
-
-#### Properties
-
-| Name      | Kind      | Type                    | Description                                                    |
-|-----------|-----------|-------------------------|----------------------------------------------------------------|
-| unique    | attribute | boolean                 | Defines whether a uniqueness constraint is set on the index.   |
-| type      | attribute | IndexType (enum)        | Defines the index type. e.g. `HASH`, `GIN`, `FULLTEXT`.        |
-| columns   | attribute | reference (list)        | The columns that comprise the index.                           |
-| on        | resource  | schema.IndexPart (list) | The index parts that comprise the index.                       |
diff --git a/doc/md/ddl/sql.mdx b/doc/md/ddl/sql.mdx
new file mode 100644
index 00000000..6a0641a4
--- /dev/null
+++ b/doc/md/ddl/sql.mdx
@@ -0,0 +1,412 @@
+---
+id: ddl-sql
+title: SQL Resources
+slug: /ddl/sql
+---
+
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+The [sqlspec](https://pkg.go.dev/ariga.io/atlas@master/sql/sqlspec) package defines
+the resource types used to describe an SQL database schema. Used with the
+[Atlas HCL](intro.md#hcl) syntax, it is easy to compose documents describing the desired
+state of a SQL database.
+
+## Schema
+
+The `schema` object describes a database schema. A `DATABASE` in MySQL and SQLite, or a `SCHEMA` in PostgreSQL.
+An HCL file can contain 1 or more schema objects.
+
+<Tabs
+defaultValue="mysql"
+values={[
+{label: 'MySQL', value: 'mysql'},
+{label: 'PostgreSQL', value: 'postgres'},
+{label: 'SQLite', value: 'sqlite'},
+]}>
+<TabItem value="mysql">
+
+In MySQL and MariaDB, the `schema` resource can contain the `charset` and `collate` attributes. Read more about them
+in [MySQL](https://dev.mysql.com/doc/refman/8.0/en/charset.html) or
+[MariaDB](https://mariadb.com/kb/en/setting-character-sets-and-collations/) websites.
+<br/>
+
+```hcl
+# Schema with attributes.
+schema "market" {
+  charset = "utf8mb4"
+  collate = "utf8mb4_0900_ai_ci"
+  comment = "A schema comment"
+}
+
+# Schema without attributes.
+schema "orders" {}
+```
+
+</TabItem>
+<TabItem value="postgres">
+
+```hcl
+schema "public" {
+  comment = "A schema comment"
+}
+
+schema "private" {}
+```
+
+</TabItem>
+<TabItem value="sqlite">
+
+Atlas does not support [attached databases](https://www.sqlite.org/lang_attach.html), and support only the default
+database (i.e. `main`).
+<br/>
+
+```hcl
+schema "main" {}
+```
+
+</TabItem>
+</Tabs>
+
+## Table
+
+A `table` describes a table in a SQL database. A table hold its columns, indexes, constraints, and additional attributes
+that are supported by the different drivers.
+
+```hcl
+table "users" {
+  schema = schema.public
+  column "id" {
+    type = int
+  }
+  column "name" {
+    type = varchar(255)
+  }
+  column "manager_id" {
+    type = int
+  }
+  primary_key {
+    columns = [
+      column.id
+    ]
+  }
+  index "idx_name" {
+    columns = [
+      column.name
+    ]
+    unique = true
+  }
+  foreign_key "manager_fk" {
+    columns = [column.manager_id]
+    ref_columns = [column.id]
+    on_delete = "CASCADE"
+    on_update = "NO ACTION"
+  }
+}
+```
+
+### Check
+
+A `check` is a child resource of a `table` that describes a `CHECK` constraint.
+
+```hcl
+table "products" {
+    column "price" {
+        type = float
+    }
+    check "positive price" {
+	    expr = "price > 0"
+    }
+}
+```
+
+## Column
+
+A `column` is a child resource of a `table`.
+
+```hcl
+column "name" {
+  type = text
+  null = false
+}
+
+column "age" {
+  type = integer
+  default = 42
+}
+
+column "active" {
+  type = tinyint(1)
+  default = true
+}
+```
+
+#### Properties
+
+| Name    | Kind      | Type                     | Description                                                |
+|---------|-----------|--------------------------|------------------------------------------------------------|
+| null    | attribute | bool                     | Defines whether the column is nullable.                    |
+| type    | attribute | *schemaspec.Type         | Defines the type of data that can be stored in the column. |
+| default | attribute | *schemaspec.LiteralValue | Defines the default value of the column.                   |
+
+## Column Types
+
+The SQL dialects supported by Atlas (Postgres, MySQL, MariaDB, and SQLite) vary in
+the types they support. At this point, the Atlas DDL does not attempt to abstract 
+away the differences between various databases. This means that the schema documents
+are tied to a specific database engine and version. This may change in a future version
+of Atlas as we plan to add "Virtual Types" support. This section lists the various
+types that are supported in each database.
+
+For a full list of supported column types, [click here](sql_types.md).
+
+## Primary Key
+
+A `primary_key` is a child resource of a `table`, and it defines the table's
+primary key. 
+
+#### Example 
+
+```hcl
+primary_key {
+  columns = [column.id]
+}
+```
+
+#### Properties
+
+| Name    | Kind      | Type                     | Description                                                    |
+|---------|-----------|--------------------------|----------------------------------------------------------------|
+| columns | resource  | reference (list)         | A list of references to columns that comprise the primary key. |
+
+## Foreign Key
+
+Foreign keys are child resources of a `table`, and it defines some columns in the table
+as references to columns in other tables. 
+
+#### Example
+
+```hcl
+foreign_key "manager_fk" {
+  columns = [column.manager_id]
+  ref_columns = [column.id]
+  on_delete = "CASCADE"
+  on_update = "NO ACTION"
+}
+```
+
+#### Properties
+
+| Name        | Kind      | Type                   | Description                               |
+|-------------|-----------|------------------------|-------------------------------------------|
+| columns     | attribute | reference (list)       | The columns that reference other columns. |
+| ref_columns | attribute | reference (list)       | The referenced columns.                   |
+| on_update   | attribute | schema.ReferenceOption | Defines what to do on update.             |
+| on_delete   | attribute | schema.ReferenceOption | Defines what to do on delete.             |
+
+## Index
+
+Indexes are child resources of a `table`, and it defines an index on the table.
+
+#### Example
+
+```hcl
+index "idx_name" {
+    columns = [
+      column.name
+    ]
+    unique = true
+}
+
+index "idx_name" {
+    on {
+        column = column.rank
+    }
+    on {
+        column = column.score
+        desc = true
+    }
+    unique = true
+}
+
+index "idx_name" {
+  type = GIN
+  columns = [column.data]
+}
+```
+
+#### Properties
+
+| Name      | Kind      | Type                    | Description                                                    |
+|-----------|-----------|-------------------------|----------------------------------------------------------------|
+| unique    | attribute | boolean                 | Defines whether a uniqueness constraint is set on the index.   |
+| type      | attribute | IndexType (enum)        | Defines the index type. e.g. `HASH`, `GIN`, `FULLTEXT`.        |
+| columns   | attribute | reference (list)        | The columns that comprise the index.                           |
+| on        | resource  | schema.IndexPart (list) | The index parts that comprise the index.                       |
+
+## Comment
+
+The `comment` attribute is an attribute of `schema`, `table`, `column`, and `index`.
+
+```hcl
+schema "public" {
+    comment = "A schema comment"
+}
+
+table "users" {
+    schema = schema.public
+    column "name" {
+        type    = text
+        comment = "A column comment"
+    }
+    index "name_idx" {
+        columns = [column.name]
+    }
+    comment = "A table comment"
+}
+```
+
+## Charset and Collation
+
+The `charset` and `collate` are attributes of `schema`, `table` and `column` and supported by MySQL, MariaDB and PostgreSQL.
+Read more about them in [MySQL](https://dev.mysql.com/doc/refman/8.0/en/charset.html),
+[MariaDB](https://mariadb.com/kb/en/setting-character-sets-and-collations/) and
+[PostgreSQL](https://www.postgresql.org/docs/current/collation.html) websites.
+
+<Tabs
+defaultValue="mysql"
+values={[
+{label: 'MySQL', value: 'mysql'},
+{label: 'PostgreSQL', value: 'postgres'},
+]}>
+<TabItem value="mysql">
+
+```hcl
+schema "public" {
+    charset = "utf8mb4"
+    collate = "utf8mb4_0900_ai_ci"
+}
+
+table "products" {
+    column "name" {
+        type    = text
+        collate = "binary"
+    }
+    collate = "utf8_general_ci"
+}
+```
+
+</TabItem>
+<TabItem value="postgres">
+
+```hcl
+schema "public" {}
+
+table "products" {
+    column "name" {
+        type    = text
+        collate = "es_ES"
+    }
+}
+```
+
+</TabItem>
+</Tabs>
+
+## Auto Increment
+
+`AUTO_INCREMENT` and `IDENTITY` columns are attributes of the `column` and `table` resource, and can be used to
+generate a unique identity for new rows.
+
+
+<Tabs
+defaultValue="mysql"
+values={[
+{label: 'MySQL', value: 'mysql'},
+{label: 'PostgreSQL', value: 'postgres'},
+{label: 'SQLite', value: 'sqlite'},
+]}>
+<TabItem value="mysql">
+
+In MySQL/MariaDB the `auto_increment` attribute can be set on columns and tables.
+<br/>
+
+```hcl
+table "users" {
+  schema = schema.public
+  column "id" {
+    null = false
+    type = bigint
+    auto_increment = true
+  }
+  primary_key  {
+    columns = [column.id]
+  }
+}
+```
+
+The `auto_increment` column can be set on the table to configure a start value other than 1.
+<br/>
+
+```hcl
+table "users" {
+  schema = schema.public
+  column "id" {
+    null = false
+    type = bigint
+    auto_increment = true
+  }
+  primary_key  {
+    columns = [column.id]
+  }
+  auto_increment = 100
+}
+```
+
+</TabItem>
+<TabItem value="postgres">
+
+PostgreSQL supports `serial` columns and the `generated as identity` syntax for versions >= 10.
+<br/>
+
+```hcl
+table "users" {
+  schema = schema.public
+  column "name" {
+    null = false
+    type = int
+    identity {
+        generated = ALWAYS
+        start = 10
+        increment = 10
+    }
+  }
+  primary_key  {
+    columns = [column.id]
+  }
+}
+```
+
+</TabItem>
+<TabItem value="sqlite">
+
+SQLite allows configuring [`AUTOINCREMENT`](https://www.sqlite.org/autoinc.html) columns using the `auto_increment`
+attribute.
+<br/>
+
+```hcl
+table "users" {
+  schema = schema.main
+  column "id" {
+    null = false
+    type = integer
+    auto_increment = true
+  }
+  primary_key  {
+    columns = [column.id]
+  }
+}
+```
+
+</TabItem>
+</Tabs>
diff --git a/doc/md/getting-started/02-inspecting.mdx b/doc/md/getting-started/02-inspecting.mdx
index 2c158cd0..fb0b836b 100644
--- a/doc/md/getting-started/02-inspecting.mdx
+++ b/doc/md/getting-started/02-inspecting.mdx
@@ -52,12 +52,12 @@ cat atlas.hcl
 View the output:
 ```hcl
 schema "example" {
-  charset   = "utf8mb4"
-  collation = "utf8mb4_0900_ai_ci"
+  charset = "utf8mb4"
+  collate = "utf8mb4_0900_ai_ci"
 }
 ```
 As you can see, Atlas inspected our (empty) database and wrote an Atlas HCL document 
-containing only a [Schema](../ddl/sql.md#schema) resource.
+containing only a [Schema](../ddl/sql.mdx#schema) resource.
 
 Next, let's create some tables in our SQL database and see how they are reflected
 in the inspected Atlas HCL document. 
@@ -112,7 +112,7 @@ table "users" {
   }
 }
 ```
-This block represents a [Table](../ddl/sql.md#table) resource with an `id`, and `name`
+This block represents a [Table](../ddl/sql.mdx#table) resource with an `id`, and `name`
 columns. The `schema` field references the `example` schema that is defined elsewhere
 in this document. In addition, the `primary_key` sub-block defines the `id` column as 
 the primary key for the table. In SQL databases, columns usually have a type attribute
@@ -158,12 +158,12 @@ table "blog_posts" {
 }
 ```
 This block represents the `blog_posts` table. In addition to the elements
-we saw in the `users` table, here we can find a [Foreign Key](../ddl/sql.md#foreign-key)
+we saw in the `users` table, here we can find a [Foreign Key](../ddl/sql.mdx#foreign-key)
 block, declaring that the `author_id` column references the `id` column on the
 `users` table.
 
 To learn more about the resource types that are available to describe SQL schemas,
-read the [SQL Syntax](../ddl/sql.md) documentation.
+read the [SQL Syntax](../ddl/sql.mdx) documentation.
 
 In the next section, we will see how we can modify our database's schema by 
 applying a modified Atlas HCL file using the Atlas CLI.
diff --git a/doc/website/sidebars.js b/doc/website/sidebars.js
index d183f371..2bacfc95 100644
--- a/doc/website/sidebars.js
+++ b/doc/website/sidebars.js
@@ -15,6 +15,7 @@ module.exports = {
         {
             type: 'category',
             label: 'Getting Started',
+            collapsed: false,
             items: [
                 'getting-started/getting-started-installation',
                 'getting-started/getting-started-inspection',
@@ -25,11 +26,12 @@ module.exports = {
         {
             type: 'category',
             label: 'Data Definition Language',
+            collapsed: false,
             items: [
-                {type: 'doc', id: 'ddl/ddl-intro', label: 'Introduction'},
-                {type: 'doc', id: 'ddl/ddl-sql', label: 'SQL'},
-                {type: 'doc', id: 'ddl/ddl-sql-types', label: 'SQL Types'},
-            ]
+                'ddl/ddl-sql',
+                'ddl/ddl-sql-types',
+                'ddl/ddl-intro',
+            ],
         },
         {
             type: 'category',
diff --git a/internal/integration/docker-compose.yaml b/internal/integration/docker-compose.yaml
index 27055bf9..220f89aa 100644
--- a/internal/integration/docker-compose.yaml
+++ b/internal/integration/docker-compose.yaml
@@ -120,4 +120,17 @@ services:
     healthcheck:
       test: mysqladmin ping -ppass
     ports:
-      - 4308:3306
\ No newline at end of file
+      - 4308:3306
+
+# Default DB test, No Password
+  tidb5:
+    platform: linux/amd64
+    image: pingcap/tidb:v5.4.0
+    ports:
+      - 4309:4000
+
+  tidb_latest:
+    platform: linux/amd64
+    image: pingcap/tidb:latest
+    ports:
+      - 4310:4000
diff --git a/sql/mysql/driver.go b/sql/mysql/driver.go
index 90d33006..6f3a2ba3 100644
--- a/sql/mysql/driver.go
+++ b/sql/mysql/driver.go
@@ -46,6 +46,14 @@ func Open(db schema.ExecQuerier) (*Driver, error) {
 	if err := sqlx.ScanOne(rows, &c.version, &c.collate, &c.charset); err != nil {
 		return nil, fmt.Errorf("mysql: scan system variables: %w", err)
 	}
+	if c.tidb() {
+		return &Driver{
+			conn:        c,
+			Differ:      &sqlx.Diff{DiffDriver: &diff{c}},
+			Inspector:   &tinspect{inspect{c}},
+			PlanApplier: &planApply{c},
+		}, nil
+	}
 	return &Driver{
 		conn:        c,
 		Differ:      &sqlx.Diff{DiffDriver: &diff{c}},
@@ -99,6 +107,11 @@ func (d *conn) mariadb() bool {
 	return strings.Index(d.version, "MariaDB") > 0
 }
 
+// tidb reports if the Driver is connected to a TiDB database.
+func (d *conn) tidb() bool {
+	return strings.Index(d.version, "TiDB") > 0
+}
+
 // compareV returns an integer comparing two versions according to
 // semantic version precedence.
 func (d *conn) compareV(w string) int {
diff --git a/sql/mysql/inspect.go b/sql/mysql/inspect.go
index f90da736..d3ff9558 100644
--- a/sql/mysql/inspect.go
+++ b/sql/mysql/inspect.go
@@ -465,14 +465,14 @@ func (i *inspect) extraAttr(t *schema.Table, c *schema.Column, extra string) err
 // the 'SHOW CREATE' command.
 func (i *inspect) showCreate(ctx context.Context, s *schema.Schema) error {
 	for _, t := range s.Tables {
-		s, ok := popShow(t)
+		st, ok := popShow(t)
 		if !ok {
 			continue
 		}
 		if err := i.createStmt(ctx, t); err != nil {
 			return err
 		}
-		if err := i.setAutoInc(s, t); err != nil {
+		if err := i.setAutoInc(st, t); err != nil {
 			return err
 		}
 		// TODO(a8m): setChecks, setIndexExpr from CREATE statement.
@@ -648,7 +648,7 @@ SELECT
 	t1.CREATE_OPTIONS
 FROM
 	INFORMATION_SCHEMA.TABLES AS t1
-	JOIN INFORMATION_SCHEMA.COLLATIONS AS t2
+	LEFT JOIN INFORMATION_SCHEMA.COLLATIONS AS t2
 	ON t1.TABLE_COLLATION = t2.COLLATION_NAME
 WHERE
 	TABLE_SCHEMA IN (%s)
diff --git a/sql/mysql/sqlspec.go b/sql/mysql/sqlspec.go
index cc15d6f6..576afd65 100644
--- a/sql/mysql/sqlspec.go
+++ b/sql/mysql/sqlspec.go
@@ -176,7 +176,7 @@ func schemaSpec(s *schema.Schema) (*sqlspec.Schema, []*sqlspec.Table, error) {
 		sc.Extra.Attrs = append(sc.Extra.Attrs, specutil.StrAttr("charset", c))
 	}
 	if c, ok := hasCollate(s.Attrs, nil); ok {
-		sc.Extra.Attrs = append(sc.Extra.Attrs, specutil.StrAttr("collation", c))
+		sc.Extra.Attrs = append(sc.Extra.Attrs, specutil.StrAttr("collate", c))
 	}
 	return sc, t, nil
 }
@@ -198,7 +198,7 @@ func tableSpec(t *schema.Table) (*sqlspec.Table, error) {
 		ts.Extra.Attrs = append(ts.Extra.Attrs, specutil.StrAttr("charset", c))
 	}
 	if c, ok := hasCollate(t.Attrs, t.Schema.Attrs); ok {
-		ts.Extra.Attrs = append(ts.Extra.Attrs, specutil.StrAttr("collation", c))
+		ts.Extra.Attrs = append(ts.Extra.Attrs, specutil.StrAttr("collate", c))
 	}
 	return ts, nil
 }
@@ -224,7 +224,7 @@ func columnSpec(c *schema.Column, t *schema.Table) (*sqlspec.Column, error) {
 		col.Extra.Attrs = append(col.Extra.Attrs, specutil.StrAttr("charset", c))
 	}
 	if c, ok := hasCollate(c.Attrs, t.Attrs); ok {
-		col.Extra.Attrs = append(col.Extra.Attrs, specutil.StrAttr("collation", c))
+		col.Extra.Attrs = append(col.Extra.Attrs, specutil.StrAttr("collate", c))
 	}
 	if o := (OnUpdate{}); sqlx.Has(c.Attrs, &o) {
 		col.Extra.Attrs = append(col.Extra.Attrs, specutil.RawAttr("on_update", o.A))
@@ -270,7 +270,12 @@ func convertCharset(spec specutil.Attrer, attrs *[]schema.Attr) error {
 		}
 		*attrs = append(*attrs, &schema.Charset{V: s})
 	}
-	if attr, ok := spec.Attr("collation"); ok {
+	// For backwards compatibility, accepts both "collate" and "collation".
+	attr, ok := spec.Attr("collate")
+	if !ok {
+		attr, ok = spec.Attr("collation")
+	}
+	if ok {
 		s, err := attr.String()
 		if err != nil {
 			return err
@@ -291,7 +296,7 @@ func hasCharset(attr []schema.Attr, parent []schema.Attr) (string, bool) {
 	return "", false
 }
 
-// hasCollate reports if the attribute contains the "collation" attribute,
+// hasCollate reports if the attribute contains the "collation"/"collate" attribute,
 // and it needs to be defined explicitly on the schema. This is true, in
 // case the element collation is different from its parent collation.
 func hasCollate(attr []schema.Attr, parent []schema.Attr) (string, bool) {
diff --git a/sql/mysql/tidb.go b/sql/mysql/tidb.go
new file mode 100644
index 00000000..bac3e845
--- /dev/null
+++ b/sql/mysql/tidb.go
@@ -0,0 +1,129 @@
+// Copyright 2021-present The Atlas Authors. All rights reserved.
+// This source code is licensed under the Apache 2.0 license found
+// in the LICENSE file in the root directory of this source tree.
+
+package mysql
+
+import (
+	"context"
+	"fmt"
+	"regexp"
+	"strings"
+
+	"ariga.io/atlas/sql/internal/sqlx"
+	"ariga.io/atlas/sql/schema"
+)
+
+type tinspect struct {
+	inspect
+}
+
+func (i *tinspect) InspectSchema(ctx context.Context, name string, opts *schema.InspectOptions) (*schema.Schema, error) {
+	s, err := i.inspect.InspectSchema(ctx, name, opts)
+	if err != nil {
+		return nil, err
+	}
+	return i.patchSchema(ctx, s)
+}
+
+func (i *tinspect) InspectRealm(ctx context.Context, opts *schema.InspectRealmOption) (*schema.Realm, error) {
+	r, err := i.inspect.InspectRealm(ctx, opts)
+	if err != nil {
+		return nil, err
+	}
+	for _, s := range r.Schemas {
+		if _, err := i.patchSchema(ctx, s); err != nil {
+			return nil, err
+		}
+	}
+	return r, nil
+}
+
+func (i *tinspect) patchSchema(ctx context.Context, s *schema.Schema) (*schema.Schema, error) {
+	for _, t := range s.Tables {
+		var createStmt CreateStmt
+		if ok := sqlx.Has(t.Attrs, &createStmt); !ok {
+			if err := i.createStmt(ctx, t); err != nil {
+				return nil, err
+			}
+		}
+		if err := i.setCollate(t); err != nil {
+			return nil, err
+		}
+		if err := i.setFKs(s, t); err != nil {
+			return nil, err
+		}
+	}
+	return s, nil
+}
+
+// e.g CONSTRAINT "" FOREIGN KEY ("foo_id") REFERENCES "foo" ("id")
+var reFK = regexp.MustCompile("(?i)CONSTRAINT\\s+[\"`]*(\\w+)[\"`]*\\s+FOREIGN\\s+KEY\\s*\\(([,\"` \\w]+)\\)\\s+REFERENCES\\s+[\"`]*(\\w+)[\"`]*\\s*\\(([,\"` \\w]+)\\)")
+
+func (i *tinspect) setFKs(s *schema.Schema, t *schema.Table) error {
+	var c CreateStmt
+	if !sqlx.Has(t.Attrs, &c) {
+		return fmt.Errorf("missing CREATE TABLE statment in attribuets for %q", t.Name)
+	}
+	for _, m := range reFK.FindAllStringSubmatch(c.S, -1) {
+		if len(m) != 5 {
+			return fmt.Errorf("unexpected number of matches for a table constraint: %q", m)
+		}
+		ctName, clmns, refTableName, refClmns := m[1], m[2], m[3], m[4]
+		fk := &schema.ForeignKey{
+			Symbol: ctName,
+			Table:  t,
+			// There is no support in TiDB for FKs so inherently there are no actions on update/delete.
+			OnUpdate: schema.NoAction,
+			OnDelete: schema.NoAction,
+		}
+		refTable, ok := s.Table(refTableName)
+		if !ok {
+			return fmt.Errorf("couldn't resolve ref table %s on ", m[3])
+		}
+		fk.RefTable = refTable
+		for _, c := range columns(s, clmns) {
+			column, ok := t.Column(c)
+			if !ok {
+				return fmt.Errorf("column %q was not found for fk %q", c, ctName)
+			}
+			fk.Columns = append(fk.Columns, column)
+		}
+		for _, c := range columns(s, refClmns) {
+			column, ok := t.Column(c)
+			if !ok {
+				return fmt.Errorf("ref column %q was not found for fk %q", c, ctName)
+			}
+			fk.RefColumns = append(fk.RefColumns, column)
+		}
+		t.ForeignKeys = append(t.ForeignKeys, fk)
+	}
+	return nil
+}
+
+// columns from the matched regex above.
+func columns(schema *schema.Schema, s string) []string {
+	names := strings.Split(s, ",")
+	for i := range names {
+		names[i] = strings.Trim(strings.TrimSpace(names[i]), "`\"")
+	}
+	return names
+}
+
+// e.g CHARSET=utf8mb4 COLLATE=utf8mb4_bin
+var reColl = regexp.MustCompile(`(?i)CHARSET\s*=\s*(\w+)\s*COLLATE\s*=\s*(\w+)`)
+
+// setCollate extracts the updated Collation from CREATE TABLE statement.
+func (i *tinspect) setCollate(t *schema.Table) error {
+	var c CreateStmt
+	if !sqlx.Has(t.Attrs, &c) {
+		return fmt.Errorf("missing CREATE TABLE statment in attribuets for %q", t.Name)
+	}
+	matches := reColl.FindStringSubmatch(c.S)
+	if len(matches) != 3 {
+		return fmt.Errorf("missing COLLATE and/or CHARSET information on CREATE TABLE statment for %q", t.Name)
+	}
+	t.SetCharset(matches[1])
+	t.SetCollation(matches[2])
+	return nil
+}


Test patch:
diff --git a/internal/integration/mysql_test.go b/internal/integration/mysql_test.go
index 9344b38c..bca2796d 100644
--- a/internal/integration/mysql_test.go
+++ b/internal/integration/mysql_test.go
@@ -37,7 +37,11 @@ func myRun(t *testing.T, fn func(*myTest)) {
 	myTests.Do(func() {
 		myTests.drivers = make(map[string]*myTest)
 		for version, port := range map[string]int{"56": 3306, "57": 3307, "8": 3308, "Maria107": 4306, "Maria102": 4307, "Maria103": 4308} {
-			db, err := sql.Open("mysql", fmt.Sprintf("root:pass@tcp(localhost:%d)/test?parseTime=True", port))
+			password := ":pass"
+			if version == "TiDB" {
+				password = ""
+			}
+			db, err := sql.Open("mysql", fmt.Sprintf("root%s@tcp(localhost:%d)/test?parseTime=True", password, port))
 			require.NoError(t, err)
 			drv, err := mysql.Open(db)
 			require.NoError(t, err)
@@ -1275,6 +1279,9 @@ func (t *myTest) defaultAttrs() []schema.Attr {
 		collation = "latin1_swedish_ci"
 	)
 	switch {
+	case t.version == "TiDB":
+		charset = "utf8mb4"
+		collation = "utf8mb4_bin"
 	case t.version == "8":
 		charset = "utf8mb4"
 		collation = "utf8mb4_0900_ai_ci"
diff --git a/internal/integration/testdata/mysql/column-add-drop.txt b/internal/integration/testdata/mysql/column-add-drop.txt
index a371a54b..4b253dd2 100644
--- a/internal/integration/testdata/mysql/column-add-drop.txt
+++ b/internal/integration/testdata/mysql/column-add-drop.txt
@@ -31,7 +31,7 @@ apply 0.hcl
 -- 1.hcl --
 schema "$db" {
     charset = "$charset"
-    collation = "$collate"
+    collate = "$collate"
 }
 
 table "users" {
@@ -59,7 +59,7 @@ CREATE TABLE `users` (
 -- 2.hcl --
 schema "$db" {
     charset = "$charset"
-    collation = "$collate"
+    collate = "$collate"
 }
 
 table "users" {
@@ -92,5 +92,5 @@ CREATE TABLE `users` (
 -- 0.hcl --
 schema "$db" {
     charset = "$charset"
-    collation = "$collate"
+    collate = "$collate"
 }
\ No newline at end of file
diff --git a/internal/integration/testdata/mysql/column-bool.txt b/internal/integration/testdata/mysql/column-bool.txt
index fbb9e6d5..e9b5bf9a 100644
--- a/internal/integration/testdata/mysql/column-bool.txt
+++ b/internal/integration/testdata/mysql/column-bool.txt
@@ -14,7 +14,7 @@ synced 3.hcl
 -- 1.hcl --
 schema "$db" {
     charset = "$charset"
-    collation = "$collate"
+    collate = "$collate"
 }
 
 table "users" {
@@ -40,7 +40,7 @@ CREATE TABLE `users` (
 -- 2.hcl --
 schema "$db" {
     charset = "$charset"
-    collation = "$collate"
+    collate = "$collate"
 }
 
 table "users" {
@@ -59,7 +59,7 @@ table "users" {
 -- 3.hcl --
 schema "$db" {
     charset = "$charset"
-    collation = "$collate"
+    collate = "$collate"
 }
 
 table "users" {
diff --git a/internal/integration/testdata/mysql/foreign-key-add.txt b/internal/integration/testdata/mysql/foreign-key-add.txt
index 68c906f3..368da449 100644
--- a/internal/integration/testdata/mysql/foreign-key-add.txt
+++ b/internal/integration/testdata/mysql/foreign-key-add.txt
@@ -20,7 +20,7 @@ cmpshow users posts 2.sql
 -- 1.hcl --
 schema "$db" {
     charset = "$charset"
-    collation = "$collate"
+    collate = "$collate"
 }
 
 table "users" {
@@ -36,7 +36,7 @@ table "users" {
 -- invalid-on-delete-action.hcl --
 schema "$db" {
     charset = "$charset"
-    collation = "$collate"
+    collate = "$collate"
 }
 
 table "users" {
@@ -70,7 +70,7 @@ table "posts" {
 -- invalid-on-update-action.hcl --
 schema "$db" {
     charset = "$charset"
-    collation = "$collate"
+    collate = "$collate"
 }
 
 table "users" {
@@ -104,7 +104,7 @@ table "posts" {
 -- 2.hcl --
 schema "$db" {
     charset = "$charset"
-    collation = "$collate"
+    collate = "$collate"
 }
 
 table "users" {
diff --git a/internal/integration/testdata/mysql/foreign-key-modify-action.txt b/internal/integration/testdata/mysql/foreign-key-modify-action.txt
index 84cd3068..8b07051d 100644
--- a/internal/integration/testdata/mysql/foreign-key-modify-action.txt
+++ b/internal/integration/testdata/mysql/foreign-key-modify-action.txt
@@ -12,7 +12,7 @@ cmpshow users posts 2.sql
 -- 1.hcl --
 schema "$db" {
     charset = "$charset"
-    collation = "$collate"
+    collate = "$collate"
 }
 
 table "users" {
@@ -73,7 +73,7 @@ CREATE TABLE `posts` (
 -- 2.hcl --
 schema "$db" {
     charset = "$charset"
-    collation = "$collate"
+    collate = "$collate"
 }
 
 table "users" {
diff --git a/internal/integration/testdata/mysql/index-add-drop.txt b/internal/integration/testdata/mysql/index-add-drop.txt
index 17dc60b9..41a9850d 100644
--- a/internal/integration/testdata/mysql/index-add-drop.txt
+++ b/internal/integration/testdata/mysql/index-add-drop.txt
@@ -21,7 +21,7 @@ cmpshow users 1.sql
 -- 1.hcl --
 schema "$db" {
     charset = "$charset"
-    collation = "$collate"
+    collate = "$collate"
 }
 
 table "users" {
@@ -44,7 +44,7 @@ CREATE TABLE `users` (
 -- 2.hcl --
 schema "$db" {
     charset = "$charset"
-    collation = "$collate"
+    collate = "$collate"
 }
 
 table "users" {
@@ -74,7 +74,7 @@ CREATE TABLE `users` (
 -- 3.hcl --
 schema "$db" {
     charset = "$charset"
-    collation = "$collate"
+    collate = "$collate"
 }
 
 table "users" {
diff --git a/internal/integration/testdata/mysql/index-desc.txt b/internal/integration/testdata/mysql/index-desc.txt
index f57273a8..7fc5c916 100644
--- a/internal/integration/testdata/mysql/index-desc.txt
+++ b/internal/integration/testdata/mysql/index-desc.txt
@@ -21,7 +21,7 @@ cmpshow users 3.sql
 -- 1.hcl --
 schema "$db" {
     charset = "$charset"
-    collation = "$collate"
+    collate = "$collate"
 }
 
 table "users" {
@@ -46,7 +46,7 @@ CREATE TABLE `users` (
 -- 2.hcl --
 schema "$db" {
     charset = "$charset"
-    collation = "$collate"
+    collate = "$collate"
 }
 
 table "users" {
@@ -71,7 +71,7 @@ CREATE TABLE `users` (
 -- 2-no-change.hcl --
 schema "$db" {
     charset = "$charset"
-    collation = "$collate"
+    collate = "$collate"
 }
 
 table "users" {
@@ -89,7 +89,7 @@ table "users" {
 -- 3.hcl --
 schema "$db" {
     charset = "$charset"
-    collation = "$collate"
+    collate = "$collate"
 }
 
 table "users" {
diff --git a/sql/mysql/sqlspec_test.go b/sql/mysql/sqlspec_test.go
index 90e4dc61..7d9530ab 100644
--- a/sql/mysql/sqlspec_test.go
+++ b/sql/mysql/sqlspec_test.go
@@ -334,10 +334,10 @@ func TestMarshalSpec_Charset(t *testing.T) {
 	const expected = `table "users" {
   schema = schema.test
   column "a" {
-    null      = false
-    type      = text
-    charset   = "latin1"
-    collation = "latin1_swedish_ci"
+    null    = false
+    type    = text
+    charset = "latin1"
+    collate = "latin1_swedish_ci"
   }
   column "b" {
     null = false
@@ -345,23 +345,23 @@ func TestMarshalSpec_Charset(t *testing.T) {
   }
 }
 table "posts" {
-  schema    = schema.test
-  charset   = "latin1"
-  collation = "latin1_swedish_ci"
+  schema  = schema.test
+  charset = "latin1"
+  collate = "latin1_swedish_ci"
   column "a" {
     null = false
     type = text
   }
   column "b" {
-    null      = false
-    type      = text
-    charset   = "utf8mb4"
-    collation = "utf8mb4_0900_ai_ci"
+    null    = false
+    type    = text
+    charset = "utf8mb4"
+    collate = "utf8mb4_0900_ai_ci"
   }
 }
 schema "test" {
-  charset   = "utf8mb4"
-  collation = "utf8mb4_0900_ai_ci"
+  charset = "utf8mb4"
+  collate = "utf8mb4_0900_ai_ci"
 }
 `
 	require.EqualValues(t, expected, string(buf))