Repository: ariga/atlas
Pull request #547 (2022-02-03 13:24:03)

sql/mysql: same inspection, but faster
`ScanSchemaFKs` and `ScanFKs` will be merged together after I'll rewrite PostgreSQL.


Patch:
diff --git a/sql/internal/sqlx/sqlx.go b/sql/internal/sqlx/sqlx.go
index a560408d..b17147f4 100644
--- a/sql/internal/sqlx/sqlx.go
+++ b/sql/internal/sqlx/sqlx.go
@@ -84,6 +84,65 @@ func ScanFKs(t *schema.Table, rows *sql.Rows) error {
 	return nil
 }
 
+// ScanSchemaFKs scans the rows and adds the foreign-key to the schema table.
+// Reference elements are added as stubs and should be linked manually by the
+// caller.
+func ScanSchemaFKs(s *schema.Schema, rows *sql.Rows) error {
+	for rows.Next() {
+		var name, table, column, tSchema, refTable, refColumn, refSchema, updateRule, deleteRule string
+		if err := rows.Scan(&name, &table, &column, &tSchema, &refTable, &refColumn, &refSchema, &updateRule, &deleteRule); err != nil {
+			return err
+		}
+		t, ok := s.Table(table)
+		if !ok {
+			return fmt.Errorf("table %q was not found in schema", table)
+		}
+		fk, ok := t.ForeignKey(name)
+		if !ok {
+			fk = &schema.ForeignKey{
+				Symbol:   name,
+				Table:    t,
+				RefTable: t,
+				OnDelete: schema.ReferenceOption(deleteRule),
+				OnUpdate: schema.ReferenceOption(updateRule),
+			}
+			switch {
+			case refTable == table:
+			case tSchema == refSchema:
+				if fk.RefTable, ok = s.Table(refTable); !ok {
+					fk.RefTable = &schema.Table{Name: refTable, Schema: s}
+				}
+			case tSchema != refSchema:
+				fk.RefTable = &schema.Table{Name: refTable, Schema: &schema.Schema{Name: refSchema}}
+			}
+			t.ForeignKeys = append(t.ForeignKeys, fk)
+		}
+		c, ok := t.Column(column)
+		if !ok {
+			return fmt.Errorf("column %q was not found for fk %q", column, fk.Symbol)
+		}
+		// Rows are ordered by ORDINAL_POSITION that specifies
+		// the position of the column in the FK definition.
+		if _, ok := fk.Column(c.Name); !ok {
+			fk.Columns = append(fk.Columns, c)
+			c.ForeignKeys = append(c.ForeignKeys, fk)
+		}
+		// Stub referenced columns or link if it's a self-reference.
+		var rc *schema.Column
+		if fk.Table != fk.RefTable {
+			rc = &schema.Column{Name: refColumn}
+		} else if c, ok := t.Column(refColumn); ok {
+			rc = c
+		} else {
+			return fmt.Errorf("referenced column %q was not found for fk %q", refColumn, fk.Symbol)
+		}
+		if _, ok := fk.RefColumn(rc.Name); !ok {
+			fk.RefColumns = append(fk.RefColumns, rc)
+		}
+	}
+	return nil
+}
+
 // LinkSchemaTables links foreign-key stub tables/columns to actual elements.
 func LinkSchemaTables(schemas []*schema.Schema) {
 	byName := make(map[string]map[string]*schema.Table)
diff --git a/sql/mysql/inspect.go b/sql/mysql/inspect.go
index 02178f90..232f10a7 100644
--- a/sql/mysql/inspect.go
+++ b/sql/mysql/inspect.go
@@ -27,23 +27,12 @@ func (i *inspect) InspectRealm(ctx context.Context, opts *schema.InspectRealmOpt
 	if err != nil {
 		return nil, err
 	}
-	realm := &schema.Realm{Schemas: schemas, Attrs: []schema.Attr{&schema.Charset{V: i.charset}, &schema.Collation{V: i.collate}}}
-	for _, s := range schemas {
-		names, err := i.tableNames(ctx, s.Name, nil)
-		if err != nil {
-			return nil, err
-		}
-		for _, name := range names {
-			t, err := i.inspectTable(ctx, name, &schema.InspectTableOptions{Schema: s.Name}, s)
-			if err != nil {
-				return nil, err
-			}
-			s.Tables = append(s.Tables, t)
-		}
-		s.Realm = realm
+	r := schema.NewRealm(schemas...).SetCharset(i.charset).SetCollation(i.collate)
+	if err := i.inspectTables(ctx, r, nil); err != nil {
+		return nil, err
 	}
 	sqlx.LinkSchemaTables(schemas)
-	return realm, nil
+	return r, nil
 }
 
 // InspectSchema returns schema descriptions of the tables in the given schema.
@@ -53,50 +42,45 @@ func (i *inspect) InspectSchema(ctx context.Context, name string, opts *schema.I
 	if err != nil {
 		return nil, err
 	}
-	if len(schemas) == 0 {
-		return nil, &schema.NotExistError{
-			Err: fmt.Errorf("mysql: schema %q was not found", name),
-		}
+	switch n := len(schemas); {
+	case n == 0:
+		return nil, &schema.NotExistError{Err: fmt.Errorf("mysql: schema %q was not found", name)}
+	case n > 1:
+		return nil, fmt.Errorf("mysql: %d schemas were found for %q", n, name)
 	}
-	s := schemas[0]
-	names, err := i.tableNames(ctx, s.Name, opts)
-	if err != nil {
+	r := schema.NewRealm(schemas...).SetCharset(i.charset).SetCollation(i.collate)
+	if err := i.inspectTables(ctx, r, opts); err != nil {
 		return nil, err
 	}
-	for _, name := range names {
-		t, err := i.inspectTable(ctx, name, &schema.InspectTableOptions{Schema: s.Name}, s)
-		if err != nil {
-			return nil, err
-		}
-		s.Tables = append(s.Tables, t)
-	}
 	sqlx.LinkSchemaTables(schemas)
-	s.Realm = &schema.Realm{Schemas: schemas, Attrs: []schema.Attr{&schema.Charset{V: i.charset}, &schema.Collation{V: i.collate}}}
-	return s, nil
+	return r.Schemas[0], nil
 }
 
-func (i *inspect) inspectTable(ctx context.Context, name string, opts *schema.InspectTableOptions, top *schema.Schema) (*schema.Table, error) {
-	t, err := i.table(ctx, name, opts)
-	if err != nil {
-		return nil, err
-	}
-	if top != nil {
-		// Link the table to its top element if provided.
-		t.Schema = top
-	}
-	if err := i.columns(ctx, t); err != nil {
-		return nil, err
-	}
-	if err := i.indexes(ctx, t); err != nil {
-		return nil, err
-	}
-	if err := i.fks(ctx, t); err != nil {
-		return nil, err
+func (i *inspect) inspectTables(ctx context.Context, r *schema.Realm, opts *schema.InspectOptions) error {
+	if err := i.tables(ctx, r, opts); err != nil {
+		return err
 	}
-	if err := i.checks(ctx, t); err != nil {
-		return nil, err
+	for _, s := range r.Schemas {
+		if len(s.Tables) == 0 {
+			continue
+		}
+		if err := i.columns(ctx, s); err != nil {
+			return err
+		}
+		if err := i.indexes(ctx, s); err != nil {
+			return err
+		}
+		if err := i.fks(ctx, s); err != nil {
+			return err
+		}
+		if err := i.checks(ctx, s); err != nil {
+			return err
+		}
+		if err := i.showCreate(ctx, s); err != nil {
+			return err
+		}
 	}
-	return t, nil
+	return nil
 }
 
 // schemas returns the list of the schemas in the database.
@@ -113,7 +97,7 @@ func (i *inspect) schemas(ctx context.Context, opts *schema.InspectRealmOption)
 			query = fmt.Sprintf(schemasQueryArgs, "= ?")
 			args = append(args, opts.Schemas[0])
 		case n > 0:
-			query = fmt.Sprintf(schemasQueryArgs, "IN ("+strings.Repeat("?, ", len(opts.Schemas)-1)+"?)")
+			query = fmt.Sprintf(schemasQueryArgs, "IN ("+nArgs(len(opts.Schemas))+")")
 			for _, s := range opts.Schemas {
 				args = append(args, s)
 			}
@@ -145,73 +129,83 @@ func (i *inspect) schemas(ctx context.Context, opts *schema.InspectRealmOption)
 	return schemas, nil
 }
 
-// table returns the table from the database, or a NotExistError if the table was not found.
-func (i *inspect) table(ctx context.Context, name string, opts *schema.InspectTableOptions) (*schema.Table, error) {
+func (i *inspect) tables(ctx context.Context, realm *schema.Realm, opts *schema.InspectOptions) error {
 	var (
-		args  = []interface{}{name}
-		query = tableQuery
+		args  []interface{}
+		query = fmt.Sprintf(tablesQuery, nArgs(len(realm.Schemas)))
 	)
-	if opts != nil && opts.Schema != "" {
-		query = tableSchemaQuery
-		args = append(args, opts.Schema)
+	for _, s := range realm.Schemas {
+		args = append(args, s.Name)
+	}
+	if opts != nil && len(opts.Tables) > 0 {
+		for _, t := range opts.Tables {
+			args = append(args, t)
+		}
+		query = fmt.Sprintf(tablesQueryArgs, nArgs(len(realm.Schemas)), nArgs(len(opts.Tables)))
 	}
 	rows, err := i.QueryContext(ctx, query, args...)
 	if err != nil {
-		return nil, err
+		return err
 	}
-	var (
-		autoinc                                       sql.NullInt64
-		tSchema, charset, collation, comment, options sql.NullString
-	)
-	if err := sqlx.ScanOne(rows, &tSchema, &charset, &collation, &autoinc, &comment, &options); err != nil {
-		if err == sql.ErrNoRows {
-			return nil, &schema.NotExistError{
-				Err: fmt.Errorf("mysql: table %q was not found", name),
-			}
+	defer rows.Close()
+	for rows.Next() {
+		var (
+			autoinc                                             sql.NullInt64
+			tSchema, name, charset, collation, comment, options sql.NullString
+		)
+		if err := rows.Scan(&tSchema, &name, &charset, &collation, &autoinc, &comment, &options); err != nil {
+			return fmt.Errorf("scan table information: %w", err)
+		}
+		if !sqlx.ValidString(tSchema) || !sqlx.ValidString(name) {
+			return fmt.Errorf("invalid schema or table name: %q.%q", tSchema.String, name.String)
+		}
+		s, ok := realm.Schema(tSchema.String)
+		if !ok {
+			return fmt.Errorf("schema %q was not found in realm", tSchema.String)
+		}
+		t := &schema.Table{Name: name.String}
+		s.AddTables(t)
+		if sqlx.ValidString(charset) {
+			t.Attrs = append(t.Attrs, &schema.Charset{
+				V: charset.String,
+			})
+		}
+		if sqlx.ValidString(collation) {
+			t.Attrs = append(t.Attrs, &schema.Collation{
+				V: collation.String,
+			})
+		}
+		if sqlx.ValidString(comment) {
+			t.Attrs = append(t.Attrs, &schema.Comment{
+				Text: comment.String,
+			})
+		}
+		if sqlx.ValidString(options) {
+			t.Attrs = append(t.Attrs, &CreateOptions{
+				V: options.String,
+			})
+		}
+		if autoinc.Valid {
+			t.Attrs = append(t.Attrs, &AutoIncrement{
+				V: autoinc.Int64,
+			})
 		}
-		return nil, err
-	}
-	t := &schema.Table{Name: name, Schema: &schema.Schema{Name: tSchema.String}}
-	if sqlx.ValidString(charset) {
-		t.Attrs = append(t.Attrs, &schema.Charset{
-			V: charset.String,
-		})
-	}
-	if sqlx.ValidString(collation) {
-		t.Attrs = append(t.Attrs, &schema.Collation{
-			V: collation.String,
-		})
-	}
-	if sqlx.ValidString(comment) {
-		t.Attrs = append(t.Attrs, &schema.Comment{
-			Text: comment.String,
-		})
-	}
-	if sqlx.ValidString(options) {
-		t.Attrs = append(t.Attrs, &CreateOptions{
-			V: options.String,
-		})
-	}
-	if autoinc.Valid {
-		t.Attrs = append(t.Attrs, &AutoIncrement{
-			V: autoinc.Int64,
-		})
 	}
-	if err := i.createStmt(ctx, t); err != nil {
-		return nil, err
+	if err := rows.Close(); err != nil {
+		return err
 	}
-	return t, nil
+	return nil
 }
 
 // columns queries and appends the columns of the given table.
-func (i *inspect) columns(ctx context.Context, t *schema.Table) error {
-	rows, err := i.QueryContext(ctx, columnsQuery, t.Schema.Name, t.Name)
+func (i *inspect) columns(ctx context.Context, s *schema.Schema) error {
+	rows, err := i.querySchema(ctx, columnsQuery, s)
 	if err != nil {
-		return fmt.Errorf("mysql: querying %q columns: %w", t.Name, err)
+		return fmt.Errorf("mysql: query schema %q columns: %w", s.Name, err)
 	}
 	defer rows.Close()
 	for rows.Next() {
-		if err := i.addColumn(t, rows); err != nil {
+		if err := i.addColumn(s, rows); err != nil {
 			return fmt.Errorf("mysql: %w", err)
 		}
 	}
@@ -219,11 +213,15 @@ func (i *inspect) columns(ctx context.Context, t *schema.Table) error {
 }
 
 // addColumn scans the current row and adds a new column from it to the table.
-func (i *inspect) addColumn(t *schema.Table, rows *sql.Rows) error {
-	var name, typ, comment, nullable, key, defaults, extra, charset, collation sql.NullString
-	if err := rows.Scan(&name, &typ, &comment, &nullable, &key, &defaults, &extra, &charset, &collation); err != nil {
+func (i *inspect) addColumn(s *schema.Schema, rows *sql.Rows) error {
+	var table, name, typ, comment, nullable, key, defaults, extra, charset, collation sql.NullString
+	if err := rows.Scan(&table, &name, &typ, &comment, &nullable, &key, &defaults, &extra, &charset, &collation); err != nil {
 		return err
 	}
+	t, ok := s.Table(table.String)
+	if !ok {
+		return fmt.Errorf("table %q was not found in schema", table.String)
+	}
 	c := &schema.Column{
 		Name: name.String,
 		Type: &schema.ColumnType{
@@ -277,44 +275,45 @@ func (i *inspect) addColumn(t *schema.Table, rows *sql.Rows) error {
 }
 
 // indexes queries and appends the indexes of the given table.
-func (i *inspect) indexes(ctx context.Context, t *schema.Table) error {
+func (i *inspect) indexes(ctx context.Context, s *schema.Schema) error {
 	query := indexesQuery
 	if i.supportsIndexExpr() {
 		query = indexesExprQuery
 	}
-	rows, err := i.QueryContext(ctx, query, t.Schema.Name, t.Name)
+	rows, err := i.querySchema(ctx, query, s)
 	if err != nil {
-		return fmt.Errorf("mysql: querying %q indexes: %w", t.Name, err)
+		return fmt.Errorf("mysql: query schema %q indexes: %w", s.Name, err)
 	}
 	defer rows.Close()
-	if err := i.addIndexes(t, rows); err != nil {
+	if err := i.addIndexes(s, rows); err != nil {
 		return err
 	}
 	return rows.Err()
 }
 
 // addIndexes scans the rows and adds the indexes to the table.
-func (i *inspect) addIndexes(t *schema.Table, rows *sql.Rows) error {
-	var (
-		hasPK bool
-		names = make(map[string]*schema.Index)
-	)
+func (i *inspect) addIndexes(s *schema.Schema, rows *sql.Rows) error {
+	hasPK := make(map[*schema.Table]bool)
 	for rows.Next() {
 		var (
 			seqno                          int
-			name, indexType                string
+			table, name, indexType         string
 			nonuniq, desc                  sql.NullBool
 			column, subPart, expr, comment sql.NullString
 		)
-		if err := rows.Scan(&name, &column, &nonuniq, &seqno, &indexType, &desc, &comment, &subPart, &expr); err != nil {
-			return fmt.Errorf("mysql: scanning indexes for table %q: %w", t.Name, err)
+		if err := rows.Scan(&table, &name, &column, &nonuniq, &seqno, &indexType, &desc, &comment, &subPart, &expr); err != nil {
+			return fmt.Errorf("mysql: scanning indexes for schema %q: %w", s.Name, err)
+		}
+		t, ok := s.Table(table)
+		if !ok {
+			return fmt.Errorf("table %q was not found in schema", table)
 		}
 		// Ignore primary keys.
 		if name == "PRIMARY" {
-			hasPK = true
+			hasPK[t] = true
 			continue
 		}
-		idx, ok := names[name]
+		idx, ok := t.Index(name)
 		if !ok {
 			idx = &schema.Index{
 				Name:   name,
@@ -329,7 +328,6 @@ func (i *inspect) addIndexes(t *schema.Table, rows *sql.Rows) error {
 					Text: comment.String,
 				})
 			}
-			names[name] = idx
 			t.Indexes = append(t.Indexes, idx)
 		}
 		// Rows are ordered by SEQ_IN_INDEX that specifies the
@@ -337,9 +335,12 @@ func (i *inspect) addIndexes(t *schema.Table, rows *sql.Rows) error {
 		part := &schema.IndexPart{SeqNo: seqno, Desc: desc.Bool}
 		switch {
 		case sqlx.ValidString(expr):
-			part.X = &schema.RawExpr{
-				X: expr.String,
-			}
+			part.X = &schema.RawExpr{X: expr.String}
+			// Functional indexes may need to be extracted from 'SHOW CREATE',
+			// because INFORMATION_SCHEMA returns them escaped and they cannot
+			// be inlined this way.
+			s := putShow(t)
+			s.indexes[idx] = append(s.indexes[idx], len(idx.Parts))
 		case sqlx.ValidString(column):
 			part.C, ok = t.Column(column.String)
 			if !ok {
@@ -360,41 +361,47 @@ func (i *inspect) addIndexes(t *schema.Table, rows *sql.Rows) error {
 		}
 		idx.Parts = append(idx.Parts, part)
 	}
-	if !hasPK && t.PrimaryKey != nil {
-		t.PrimaryKey = nil
+	for _, t := range s.Tables {
+		if !hasPK[t] && t.PrimaryKey != nil {
+			t.PrimaryKey = nil
+		}
 	}
 	return nil
 }
 
 // fks queries and appends the foreign keys of the given table.
-func (i *inspect) fks(ctx context.Context, t *schema.Table) error {
-	rows, err := i.QueryContext(ctx, fksQuery, t.Schema.Name, t.Name)
+func (i *inspect) fks(ctx context.Context, s *schema.Schema) error {
+	rows, err := i.querySchema(ctx, fksQuery, s)
 	if err != nil {
-		return fmt.Errorf("mysql: querying %q foreign keys: %w", t.Name, err)
+		return fmt.Errorf("mysql: querying %q foreign keys: %w", s.Name, err)
 	}
 	defer rows.Close()
-	if err := sqlx.ScanFKs(t, rows); err != nil {
+	if err := sqlx.ScanSchemaFKs(s, rows); err != nil {
 		return fmt.Errorf("mysql: %w", err)
 	}
 	return rows.Err()
 }
 
 // checks queries and appends the check constraints of the given table.
-func (i *inspect) checks(ctx context.Context, t *schema.Table) error {
+func (i *inspect) checks(ctx context.Context, s *schema.Schema) error {
 	query, ok := i.supportsCheck()
 	if !ok {
 		return nil
 	}
-	rows, err := i.QueryContext(ctx, query, t.Schema.Name, t.Name)
+	rows, err := i.querySchema(ctx, query, s)
 	if err != nil {
-		return fmt.Errorf("mysql: querying %q check constraints: %w", t.Name, err)
+		return fmt.Errorf("mysql: querying %q check constraints: %w", s.Name, err)
 	}
 	defer rows.Close()
 	for rows.Next() {
-		var name, clause, enforced sql.NullString
-		if err := rows.Scan(&name, &clause, &enforced); err != nil {
+		var table, name, clause, enforced sql.NullString
+		if err := rows.Scan(&table, &name, &clause, &enforced); err != nil {
 			return fmt.Errorf("mysql: %w", err)
 		}
+		t, ok := s.Table(table.String)
+		if !ok {
+			return fmt.Errorf("table %q was not found in schema", table.String)
+		}
 		check := &schema.Check{
 			Name: name.String,
 			Expr: unescape(clause.String),
@@ -418,30 +425,14 @@ func (i *inspect) checks(ctx context.Context, t *schema.Table) error {
 			check.Attrs = append(check.Attrs, &Enforced{V: false})
 		}
 		t.Attrs = append(t.Attrs, check)
+		// CHECK constraints need to be extracted from 'SHOW CREATE', because
+		// INFORMATION_SCHEMA returns them escaped and they cannot be used on
+		// 'CREATE' this way.
+		putShow(t).checks = true
 	}
 	return rows.Err()
 }
 
-// tableNames returns a list of all tables exist in the schema.
-func (i *inspect) tableNames(ctx context.Context, schema string, opts *schema.InspectOptions) ([]string, error) {
-	query, args := tablesQuery, []interface{}{schema}
-	if opts != nil && len(opts.Tables) > 0 {
-		query = fmt.Sprintf(tablesQueryArgs, strings.Repeat("?, ", len(opts.Tables)-1)+"?")
-		for _, s := range opts.Tables {
-			args = append(args, s)
-		}
-	}
-	rows, err := i.QueryContext(ctx, query, args...)
-	if err != nil {
-		return nil, fmt.Errorf("mysql: querying schema tables: %w", err)
-	}
-	names, err := sqlx.ScanStrings(rows)
-	if err != nil {
-		return nil, fmt.Errorf("mysql: scanning table names: %w", err)
-	}
-	return names, nil
-}
-
 var reTimeOnUpdate = regexp.MustCompile(`(?i)^(?:default_generated )?on update (current_timestamp(?:\(\d?\))?)$`)
 
 // extraAttr parses the EXTRA column from the INFORMATION_SCHEMA.COLUMNS table
@@ -455,16 +446,11 @@ func (i *inspect) extraAttr(t *schema.Table, c *schema.Column, extra string) err
 		// and it is handled in Driver.addColumn.
 	case el == autoIncrement:
 		a := &AutoIncrement{}
-		// A table can have only one AUTO_INCREMENT column. If it was returned as NULL
-		// from INFORMATION_SCHEMA, it is due to information_schema_stats_expiry and we
-		// need to extract it from the 'CREATE TABLE' command.
 		if !sqlx.Has(t.Attrs, a) {
-			inc, err := i.autoinc(t)
-			if err != nil {
-				return err
-			}
-			*a = *inc
-			t.Attrs = append(t.Attrs, a)
+			// A table can have only one AUTO_INCREMENT column. If it was returned as NULL
+			// from INFORMATION_SCHEMA, it is due to information_schema_stats_expiry and we
+			// need to extract it from the 'CREATE TABLE' command.
+			putShow(t).auto = a
 		}
 		c.Attrs = append(c.Attrs, a)
 	case reTimeOnUpdate.MatchString(extra):
@@ -475,23 +461,50 @@ func (i *inspect) extraAttr(t *schema.Table, c *schema.Column, extra string) err
 	return nil
 }
 
+// showCreate sets and fixes schema elements that require information from
+// the 'SHOW CREATE' command.
+func (i *inspect) showCreate(ctx context.Context, s *schema.Schema) error {
+	for _, t := range s.Tables {
+		s, ok := popShow(t)
+		if !ok {
+			continue
+		}
+		if err := i.createStmt(ctx, t); err != nil {
+			return err
+		}
+		if err := i.setAutoInc(s, t); err != nil {
+			return err
+		}
+		// TODO(a8m): setChecks, setIndexExpr from CREATE statement.
+	}
+	return nil
+}
+
 var reAutoinc = regexp.MustCompile(`(?i)\s+AUTO_INCREMENT\s*=\s*(\d+)\s+`)
 
-// autoinc extracts the updated AUTO_INCREMENT from CREATE TABLE.
-func (i *inspect) autoinc(t *schema.Table) (*AutoIncrement, error) {
+// setAutoInc extracts the updated AUTO_INCREMENT from CREATE TABLE.
+func (i *inspect) setAutoInc(s *showTable, t *schema.Table) error {
+	if s.auto == nil {
+		return nil
+	}
 	var c CreateStmt
 	if !sqlx.Has(t.Attrs, &c) {
-		return nil, fmt.Errorf("missing CREATE TABLE statement in attribuets for %q", t.Name)
+		return fmt.Errorf("missing CREATE TABLE statment in attribuets for %q", t.Name)
+	}
+	if sqlx.Has(t.Attrs, &AutoIncrement{}) {
+		return fmt.Errorf("unexpected AUTO_INCREMENT attributes for table: %q", t.Name)
 	}
 	matches := reAutoinc.FindStringSubmatch(c.S)
 	if len(matches) != 2 {
-		return &AutoIncrement{}, nil
+		return nil
 	}
 	v, err := strconv.ParseInt(matches[1], 10, 64)
 	if err != nil {
-		return nil, err
+		return err
 	}
-	return &AutoIncrement{V: v}, nil
+	s.auto.V = v
+	t.Attrs = append(t.Attrs, s.auto)
+	return nil
 }
 
 // createStmt loads the CREATE TABLE statement for the table.
@@ -596,6 +609,16 @@ func (i *inspect) marDefaultExpr(c *schema.Column, x string) schema.Expr {
 	return &schema.RawExpr{X: x}
 }
 
+func (i *inspect) querySchema(ctx context.Context, query string, s *schema.Schema) (*sql.Rows, error) {
+	args := []interface{}{s.Name}
+	for _, t := range s.Tables {
+		args = append(args, t.Name)
+	}
+	return i.QueryContext(ctx, fmt.Sprintf(query, nArgs(len(s.Tables))), args...)
+}
+
+func nArgs(n int) string { return strings.Repeat("?, ", n-1) + "?" }
+
 const (
 	// Query to list system variables.
 	variablesQuery = "SELECT @@version, @@collation_server, @@character_set_server"
@@ -606,23 +629,17 @@ const (
 	// Query to list specific database schemas.
 	schemasQueryArgs = "SELECT `SCHEMA_NAME`, `DEFAULT_CHARACTER_SET_NAME`, `DEFAULT_COLLATION_NAME` from `INFORMATION_SCHEMA`.`SCHEMATA` WHERE `SCHEMA_NAME` %s ORDER BY `SCHEMA_NAME`"
 
-	// Query to list schema tables.
-	tablesQuery = "SELECT `TABLE_NAME` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_TYPE` = 'BASE TABLE' AND `TABLE_SCHEMA` = ? ORDER BY `TABLE_NAME`"
-
-	// Query to list specific schema tables.
-	tablesQueryArgs = "SELECT `TABLE_NAME` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_TYPE` = 'BASE TABLE' AND `TABLE_SCHEMA` = ? AND `TABLE_NAME` IN (%s) ORDER BY `TABLE_NAME`"
-
 	// Query to list table columns.
-	columnsQuery = "SELECT `COLUMN_NAME`, `COLUMN_TYPE`, `COLUMN_COMMENT`, `IS_NULLABLE`, `COLUMN_KEY`, `COLUMN_DEFAULT`, `EXTRA`, `CHARACTER_SET_NAME`, `COLLATION_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ? ORDER BY `ORDINAL_POSITION`"
+	columnsQuery = "SELECT `TABLE_NAME`, `COLUMN_NAME`, `COLUMN_TYPE`, `COLUMN_COMMENT`, `IS_NULLABLE`, `COLUMN_KEY`, `COLUMN_DEFAULT`, `EXTRA`, `CHARACTER_SET_NAME`, `COLLATION_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` IN (%s) ORDER BY `ORDINAL_POSITION`"
 
 	// Query to list table indexes.
-	indexesQuery     = "SELECT `INDEX_NAME`, `COLUMN_NAME`, `NON_UNIQUE`, `SEQ_IN_INDEX`, `INDEX_TYPE`, UPPER(`COLLATION`) = 'D' AS `DESC`, `INDEX_COMMENT`, `SUB_PART`, NULL AS `EXPRESSION` FROM `INFORMATION_SCHEMA`.`STATISTICS` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ? ORDER BY `index_name`, `seq_in_index`"
-	indexesExprQuery = "SELECT `INDEX_NAME`, `COLUMN_NAME`, `NON_UNIQUE`, `SEQ_IN_INDEX`, `INDEX_TYPE`, UPPER(`COLLATION`) = 'D' AS `DESC`, `INDEX_COMMENT`, `SUB_PART`, `EXPRESSION` FROM `INFORMATION_SCHEMA`.`STATISTICS` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ? ORDER BY `index_name`, `seq_in_index`"
+	indexesQuery     = "SELECT `TABLE_NAME`, `INDEX_NAME`, `COLUMN_NAME`, `NON_UNIQUE`, `SEQ_IN_INDEX`, `INDEX_TYPE`, UPPER(`COLLATION`) = 'D' AS `DESC`, `INDEX_COMMENT`, `SUB_PART`, NULL AS `EXPRESSION` FROM `INFORMATION_SCHEMA`.`STATISTICS` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` IN (%s) ORDER BY `index_name`, `seq_in_index`"
+	indexesExprQuery = "SELECT `TABLE_NAME`, `INDEX_NAME`, `COLUMN_NAME`, `NON_UNIQUE`, `SEQ_IN_INDEX`, `INDEX_TYPE`, UPPER(`COLLATION`) = 'D' AS `DESC`, `INDEX_COMMENT`, `SUB_PART`, `EXPRESSION` FROM `INFORMATION_SCHEMA`.`STATISTICS` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` IN (%s) ORDER BY `index_name`, `seq_in_index`"
 
-	// Query to list table information.
-	tableQuery = `
+	tablesQuery = `
 SELECT
 	t1.TABLE_SCHEMA,
+	t1.TABLE_NAME,
 	t2.CHARACTER_SET_NAME,
 	t1.TABLE_COLLATION,
 	t1.AUTO_INCREMENT,
@@ -633,12 +650,15 @@ FROM
 	JOIN INFORMATION_SCHEMA.COLLATIONS AS t2
 	ON t1.TABLE_COLLATION = t2.COLLATION_NAME
 WHERE
-	TABLE_NAME = ?
-	AND TABLE_SCHEMA = (SELECT DATABASE())
+	TABLE_SCHEMA IN (%s)
+ORDER BY
+	TABLE_SCHEMA, TABLE_NAME
 `
-	tableSchemaQuery = `
+
+	tablesQueryArgs = `
 SELECT
 	t1.TABLE_SCHEMA,
+	t1.TABLE_NAME,
 	t2.CHARACTER_SET_NAME,
 	t1.TABLE_COLLATION,
 	t1.AUTO_INCREMENT,
@@ -649,13 +669,15 @@ FROM
 	JOIN INFORMATION_SCHEMA.COLLATIONS AS t2
 	ON t1.TABLE_COLLATION = t2.COLLATION_NAME
 WHERE
-	TABLE_NAME = ?
-	AND TABLE_SCHEMA = ?
+	TABLE_SCHEMA IN (%s)
+	AND TABLE_NAME IN (%s)
+ORDER BY
+	TABLE_SCHEMA, TABLE_NAME
 `
 
 	// Query to list table check constraints.
-	myChecksQuery  = `SELECT t1.CONSTRAINT_NAME, t2.CHECK_CLAUSE, t1.ENFORCED` + checksQuery
-	marChecksQuery = `SELECT t1.CONSTRAINT_NAME, t2.CHECK_CLAUSE, "YES" AS ENFORCED` + checksQuery
+	myChecksQuery  = `SELECT t1.TABLE_NAME, t1.CONSTRAINT_NAME, t2.CHECK_CLAUSE, t1.ENFORCED` + checksQuery
+	marChecksQuery = `SELECT t1.TABLE_NAME, t1.CONSTRAINT_NAME, t2.CHECK_CLAUSE, "YES" AS ENFORCED` + checksQuery
 	checksQuery    = `
 FROM
 	INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t1
@@ -664,7 +686,7 @@ FROM
 WHERE
 	t1.CONSTRAINT_TYPE = 'CHECK'
 	AND t1.TABLE_SCHEMA = ?
-	AND t1.TABLE_NAME = ?
+	AND t1.TABLE_NAME IN (%s)
 ORDER BY
 	t1.CONSTRAINT_NAME
 `
@@ -692,7 +714,7 @@ FROM
 WHERE
 	t2.CONSTRAINT_TYPE = 'FOREIGN KEY'
 	AND t1.TABLE_SCHEMA = ?
-	AND t1.TABLE_NAME = ?
+	AND t1.TABLE_NAME IN (%s)
 ORDER BY
 	t1.CONSTRAINT_NAME,
 	t1.ORDINAL_POSITION`
@@ -766,4 +788,38 @@ type (
 		schema.Type
 		Values []string
 	}
+
+	// putShow is an intermediate table attribute used
+	// on inspection to indicate if the 'SHOW TABLE' is
+	// required and for what.
+	showTable struct {
+		schema.Attr
+		// AUTO_INCREMENT value to due missing value in information_schema.
+		auto *AutoIncrement
+		// checks expressions formatted differently from exist in 'SHOW CREATE'.
+		checks bool
+		// indexes that contain expressions.
+		indexes map[*schema.Index][]int
+	}
 )
+
+func putShow(t *schema.Table) *showTable {
+	for i := range t.Attrs {
+		if s, ok := t.Attrs[i].(*showTable); ok {
+			return s
+		}
+	}
+	s := &showTable{indexes: make(map[*schema.Index][]int)}
+	t.Attrs = append(t.Attrs, s)
+	return s
+}
+
+func popShow(t *schema.Table) (*showTable, bool) {
+	for i := range t.Attrs {
+		if s, ok := t.Attrs[i].(*showTable); ok {
+			t.Attrs = append(t.Attrs[:i], t.Attrs[i+1:]...)
+			return s, true
+		}
+	}
+	return nil, false
+}
diff --git a/sql/schema/dsl.go b/sql/schema/dsl.go
index e89e4870..7207108e 100644
--- a/sql/schema/dsl.go
+++ b/sql/schema/dsl.go
@@ -70,6 +70,41 @@ func (s *Schema) AddTables(tables ...*Table) *Schema {
 	return s
 }
 
+// NewRealm creates a new Realm.
+func NewRealm(schemas ...*Schema) *Realm {
+	r := &Realm{Schemas: schemas}
+	for _, s := range schemas {
+		s.Realm = r
+	}
+	return r
+}
+
+// SetCharset sets or appends the Charset attribute
+// to the realm with the given value.
+func (r *Realm) SetCharset(v string) *Realm {
+	replaceOrAppend(&r.Attrs, &Charset{V: v})
+	return r
+}
+
+// UnsetCharset unsets the Charset attribute.
+func (r *Realm) UnsetCharset() *Realm {
+	del(&r.Attrs, &Charset{})
+	return r
+}
+
+// SetCollation sets or appends the Collation attribute
+// to the realm with the given value.
+func (r *Realm) SetCollation(v string) *Realm {
+	replaceOrAppend(&r.Attrs, &Collation{V: v})
+	return r
+}
+
+// UnsetCollation the Collation attribute.
+func (r *Realm) UnsetCollation() *Realm {
+	del(&r.Attrs, &Collation{})
+	return r
+}
+
 // NewTable creates a new Table.
 func NewTable(name string) *Table {
 	return &Table{Name: name}


Test patch:
diff --git a/sql/mysql/inspect_test.go b/sql/mysql/inspect_test.go
index 48243bc0..05f6b690 100644
--- a/sql/mysql/inspect_test.go
+++ b/sql/mysql/inspect_test.go
@@ -17,6 +17,17 @@ import (
 	"github.com/stretchr/testify/require"
 )
 
+// Single table queries used by the different tests.
+var (
+	queryFKs         = sqltest.Escape(fmt.Sprintf(fksQuery, "?"))
+	queryTable       = sqltest.Escape(fmt.Sprintf(tablesQuery, "?"))
+	queryColumns     = sqltest.Escape(fmt.Sprintf(columnsQuery, "?"))
+	queryIndexes     = sqltest.Escape(fmt.Sprintf(indexesQuery, "?"))
+	queryIndexesExpr = sqltest.Escape(fmt.Sprintf(indexesExprQuery, "?"))
+	queryMyChecks    = sqltest.Escape(fmt.Sprintf(myChecksQuery, "?"))
+	queryMarChecks   = sqltest.Escape(fmt.Sprintf(marChecksQuery, "?"))
+)
+
 func TestDriver_InspectTable(t *testing.T) {
 	tests := []struct {
 		name    string
@@ -27,15 +38,34 @@ func TestDriver_InspectTable(t *testing.T) {
 		{
 			name: "table collation",
 			before: func(m mock) {
-				m.ExpectQuery(sqltest.Escape(tableSchemaQuery)).
-					WithArgs("users", "public").
+				m.ExpectQuery(queryTable).
+					WithArgs("public").
+					WillReturnRows(sqltest.Rows(`
++--------------+--------------+--------------------+--------------------+----------------+---------------+--------------------+
+| TABLE_SCHEMA | TABLE_NAME   | CHARACTER_SET_NAME | TABLE_COLLATION    | AUTO_INCREMENT | TABLE_COMMENT | CREATE_OPTIONS     |
++--------------+--------------+--------------------+--------------------+----------------+---------------+--------------------+
+| public       | users        | utf8mb4            | utf8mb4_0900_ai_ci | nil            | Comment       | COMPRESSION="ZLIB" |
++--------------+--------------+--------------------+--------------------+----------------+---------------+--------------------+
+`))
+				m.ExpectQuery(queryColumns).
+					WithArgs("public", "users").
+					WillReturnRows(sqltest.Rows(`
++--------------------+--------------------+----------------------+----------------------+-------------+------------+----------------+----------------+--------------------+----------------+
+| table_name         | column_name        | column_type          | column_comment       | is_nullable | column_key | column_default | extra          | character_set_name | collation_name |
++--------------------+--------------------+----------------------+----------------------+-------------+------------+----------------+----------------+--------------------+----------------+
+| users              | id                 | bigint(20)           |                      | NO          | PRI        | NULL           | auto_increment | NULL               | NULL           |
++--------------------+--------------------+----------------------+----------------------+-------------+------------+----------------+----------------+--------------------+----------------+
+`))
+				m.ExpectQuery(queryIndexesExpr).
+					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
-+--------------+--------------------+--------------------+----------------+---------------+--------------------+
-| TABLE_SCHEMA | CHARACTER_SET_NAME | TABLE_COLLATION    | AUTO_INCREMENT | TABLE_COMMENT | CREATE_OPTIONS     |
-+--------------+--------------------+--------------------+----------------+---------------+--------------------+
-| public       | utf8mb4            | utf8mb4_0900_ai_ci | nil            | Comment       | COMPRESSION="ZLIB" |
-+--------------+--------------------+--------------------+----------------+---------------+--------------------+
++--------------------+--------------+-------------+------------+--------------+--------------+----------+--------------+------------+------------------+
+| TABLE_NAME         | INDEX_NAME   | COLUMN_NAME | NON_UNIQUE | SEQ_IN_INDEX | INDEX_TYPE   | DESC     | COMMENT      | SUB_PART   | EXPRESSION       |
++--------------------+--------------+-------------+------------+--------------+--------------+----------+--------------+------------+------------------+
+| users              | PRIMARY      | id          |          0 |            1 | BTREE        | 0        |              |       NULL |      NULL        |
++--------------------+--------------+-------------+------------+--------------+--------------+----------+--------------+------------+------------------+
 `))
+				m.noFKs()
 				m.ExpectQuery(sqltest.Escape("SHOW CREATE TABLE `public`.`users`")).
 					WillReturnRows(sqltest.Rows(`
 +-------+---------------------------------------------------------------------------------------------------------------------------------------------+
@@ -44,25 +74,6 @@ func TestDriver_InspectTable(t *testing.T) {
 | users | CREATE TABLE users (id bigint NOT NULL AUTO_INCREMENT) ENGINE=InnoDB AUTO_INCREMENT=55834574848 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
 +-------+---------------------------------------------------------------------------------------------------------------------------------------------+
 `))
-				m.ExpectQuery(sqltest.Escape(columnsQuery)).
-					WithArgs("public", "users").
-					WillReturnRows(sqltest.Rows(`
-+--------------------+----------------------+----------------------+-------------+------------+----------------+----------------+--------------------+----------------+
-| column_name        | column_type          | column_comment       | is_nullable | column_key | column_default | extra          | character_set_name | collation_name |
-+--------------------+----------------------+----------------------+-------------+------------+----------------+----------------+--------------------+----------------+
-| id                 | bigint(20)           |                      | NO          | PRI        | NULL           | auto_increment | NULL               | NULL           |
-+--------------------+----------------------+----------------------+-------------+------------+----------------+----------------+--------------------+----------------+
-`))
-				m.ExpectQuery(sqltest.Escape(indexesExprQuery)).
-					WithArgs("public", "users").
-					WillReturnRows(sqltest.Rows(`
-+--------------+-------------+------------+--------------+--------------+----------+--------------+------------+------------------+
-| INDEX_NAME   | COLUMN_NAME | NON_UNIQUE | SEQ_IN_INDEX | INDEX_TYPE   | DESC     | COMMENT      | SUB_PART   | EXPRESSION       |
-+--------------+-------------+------------+--------------+--------------+----------+--------------+------------+------------------+
-| PRIMARY      | id          |          0 |            1 | BTREE        | 0        |              |       NULL |      NULL        |
-+--------------+-------------+------------+--------------+--------------+----------+--------------+------------+------------------+
-`))
-				m.noFKs()
 			},
 			expect: func(require *require.Assertions, t *schema.Table, err error) {
 				require.NoError(err)
@@ -85,28 +96,36 @@ func TestDriver_InspectTable(t *testing.T) {
 		{
 			name: "int types",
 			before: func(m mock) {
-				m.tableExists("public", "users", true)
-				m.ExpectQuery(sqltest.Escape(columnsQuery)).
+				m.ExpectQuery(queryTable).
+					WithArgs("public").
+					WillReturnRows(sqltest.Rows(`
++--------------+--------------+--------------------+--------------------+----------------+---------------+--------------------+
+| TABLE_SCHEMA | TABLE_NAME   | CHARACTER_SET_NAME | TABLE_COLLATION    | AUTO_INCREMENT | TABLE_COMMENT | CREATE_OPTIONS     |
++--------------+--------------+--------------------+--------------------+----------------+---------------+--------------------+
+| public       | users        | utf8mb4            | utf8mb4_0900_ai_ci | nil            | Comment       | COMPRESSION="ZLIB" |
++--------------+--------------+--------------------+--------------------+----------------+---------------+--------------------+
+`))
+				m.ExpectQuery(queryColumns).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
-+--------------------+------------------------------+----------------------+-------------+------------+----------------+----------------+--------------------+----------------+
-| column_name        | column_type                  | column_comment       | is_nullable | column_key | column_default | extra          | character_set_name | collation_name |
-+--------------------+------------------------------+----------------------+-------------+------------+----------------+----------------+--------------------+----------------+
-| id                 | bigint(20)                   |                      | NO          | PRI        | NULL           |                | NULL               | NULL           |
-| v57_tiny           | tinyint(1)                   |                      | NO          |            | NULL           |                | NULL               | NULL           |
-| v57_tiny_unsigned  | tinyint(4) unsigned          |                      | NO          |            | NULL           |                | NULL               | NULL           |
-| v57_small          | smallint(6)                  |                      | NO          |            | NULL           |                | NULL               | NULL           |
-| v57_small_unsigned | smallint(6) unsigned         |                      | NO          |            | NULL           |                | NULL               | NULL           |
-| v57_int            | bigint(11)                   |                      | NO          |            | NULL           |                | NULL               | NULL           |
-| v57_int_unsigned   | bigint(11) unsigned          |                      | NO          |            | NULL           |                | NULL               | NULL           |
-| v8_tiny            | tinyint                      |                      | NO          |            | NULL           |                | NULL               | NULL           |
-| v8_tiny_unsigned   | tinyint unsigned             |                      | NO          |            | NULL           |                | NULL               | NULL           |
-| v8_small           | smallint                     |                      | NO          |            | NULL           |                | NULL               | NULL           |
-| v8_small_unsigned  | smallint unsigned            |                      | NO          |            | NULL           |                | NULL               | NULL           |
-| v8_big             | bigint                       |                      | NO          |            | NULL           |                | NULL               | NULL           |
-| v8_big_unsigned    | bigint unsigned              | comment              | NO          |            | NULL           |                | NULL               | NULL           |
-| v8_big_zerofill    | bigint(20) unsigned zerofill | comment              | NO          |            | NULL           |                | NULL               | NULL           |
-+--------------------+------------------------------+----------------------+-------------+------------+----------------+----------------+--------------------+----------------+
++------------+--------------------+------------------------------+----------------------+-------------+------------+----------------+----------------+--------------------+----------------+
+| table_name | column_name        | column_type                  | column_comment       | is_nullable | column_key | column_default | extra          | character_set_name | collation_name |
++----------- +--------------------+------------------------------+----------------------+-------------+------------+----------------+----------------+--------------------+----------------+
+| users      | id                 | bigint(20)                   |                      | NO          | PRI        | NULL           |                | NULL               | NULL           |
+| users      | v57_tiny           | tinyint(1)                   |                      | NO          |            | NULL           |                | NULL               | NULL           |
+| users      | v57_tiny_unsigned  | tinyint(4) unsigned          |                      | NO          |            | NULL           |                | NULL               | NULL           |
+| users      | v57_small          | smallint(6)                  |                      | NO          |            | NULL           |                | NULL               | NULL           |
+| users      | v57_small_unsigned | smallint(6) unsigned         |                      | NO          |            | NULL           |                | NULL               | NULL           |
+| users      | v57_int            | bigint(11)                   |                      | NO          |            | NULL           |                | NULL               | NULL           |
+| users      | v57_int_unsigned   | bigint(11) unsigned          |                      | NO          |            | NULL           |                | NULL               | NULL           |
+| users      | v8_tiny            | tinyint                      |                      | NO          |            | NULL           |                | NULL               | NULL           |
+| users      | v8_tiny_unsigned   | tinyint unsigned             |                      | NO          |            | NULL           |                | NULL               | NULL           |
+| users      | v8_small           | smallint                     |                      | NO          |            | NULL           |                | NULL               | NULL           |
+| users      | v8_small_unsigned  | smallint unsigned            |                      | NO          |            | NULL           |                | NULL               | NULL           |
+| users      | v8_big             | bigint                       |                      | NO          |            | NULL           |                | NULL               | NULL           |
+| users      | v8_big_unsigned    | bigint unsigned              | comment              | NO          |            | NULL           |                | NULL               | NULL           |
+| users      | v8_big_zerofill    | bigint(20) unsigned zerofill | comment              | NO          |            | NULL           |                | NULL               | NULL           |
++------------+--------------------+------------------------------+----------------------+-------------+------------+----------------+----------------+--------------------+----------------+
 `))
 				m.noIndexes()
 				m.noFKs()
@@ -137,29 +156,37 @@ func TestDriver_InspectTable(t *testing.T) {
 			version: "10.7.1-MariaDB",
 			before: func(m mock) {
 				m.tableExists("public", "users", true)
-				m.ExpectQuery(sqltest.Escape(columnsQuery)).
+				m.ExpectQuery(queryColumns).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
-+---------------+------------------------------+----------------------+-------------+------------+----------------+----------------+--------------------+----------------+
-| column_name   | column_type                  | column_comment       | is_nullable | column_key | column_default | extra          | character_set_name | collation_name |
-+---------------+------------------------------+----------------------+-------------+------------+----------------+----------------+--------------------+----------------+
-| id            | bigint(20)                   |                      | NO          | PRI        | NULL           |                | NULL               | NULL           |
-| tiny_int      | tinyint(1)                   |                      | NO          |            | NULL           |                | NULL               | NULL           |
-| longtext      | longtext                     |                      | NO          |            | NULL           |                | NULL               | NULL           |
-| jsonc         | longtext                     |                      | NO          |            | NULL           |                | NULL               | NULL           |
-+---------------+------------------------------+----------------------+-------------+------------+----------------+----------------+--------------------+----------------+
++------------+----------------+------------------------------+----------------------+-------------+------------+----------------+----------------+--------------------+----------------+
+| table_name |  column_name   | column_type                  | column_comment       | is_nullable | column_key | column_default | extra          | character_set_name | collation_name |
++------------+----------------+------------------------------+----------------------+-------------+------------+----------------+----------------+--------------------+----------------+
+| users      |  id            | bigint(20)                   |                      | NO          | PRI        | NULL           |                | NULL               | NULL           |
+| users      |  tiny_int      | tinyint(1)                   |                      | NO          |            | NULL           |                | NULL               | NULL           |
+| users      |  longtext      | longtext                     |                      | NO          |            | NULL           |                | NULL               | NULL           |
+| users      |  jsonc         | longtext                     |                      | NO          |            | NULL           |                | NULL               | NULL           |
++------------+----------------+------------------------------+----------------------+-------------+------------+----------------+----------------+--------------------+----------------+
 `))
-				m.ExpectQuery(sqltest.Escape(indexesQuery)).
-					WillReturnRows(sqlmock.NewRows([]string{"index_name", "column_name", "non_unique", "key_part", "expression"}))
+				m.ExpectQuery(queryIndexes).
+					WillReturnRows(sqlmock.NewRows([]string{"table_name", "index_name", "column_name", "non_unique", "key_part", "expression"}))
 				m.noFKs()
-				m.ExpectQuery(sqltest.Escape(marChecksQuery)).
+				m.ExpectQuery(queryMarChecks).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
-+-------------------+-------------------------------------------+------------+
-| CONSTRAINT_NAME   | CHECK_CLAUSE                              |  ENFORCED  |
-+-------------------+-------------------------------------------+------------+
-| jsonc             | json_valid(` + "`jsonc`" + `)             |  YES       |
-+-------------------+-------------------------------------------+------------+
++--------+------------------+-------------------------------------------+------------+
+| table  | CONSTRAINT_NAME  | CHECK_CLAUSE                              |  ENFORCED  |
++--------+------------------+-------------------------------------------+------------+
+| users  | jsonc            | json_valid(` + "`jsonc`" + `)             |  YES       |
++--------+------------------+-------------------------------------------+------------+
+`))
+				m.ExpectQuery(sqltest.Escape("SHOW CREATE TABLE `public`.`users`")).
+					WillReturnRows(sqltest.Rows(`
++-------+---------------------------------------------------------------------------------------------------------------------------------------------+
+| Table | Create Table                                                                                                                                |
++-------+---------------------------------------------------------------------------------------------------------------------------------------------+
+| users | CREATE TABLE users (id bigint NOT NULL AUTO_INCREMENT) ENGINE=InnoDB AUTO_INCREMENT=55834574848 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
++-------+---------------------------------------------------------------------------------------------------------------------------------------------+
 `))
 			},
 			expect: func(require *require.Assertions, t *schema.Table, err error) {
@@ -177,15 +204,15 @@ func TestDriver_InspectTable(t *testing.T) {
 			name: "decimal types",
 			before: func(m mock) {
 				m.tableExists("public", "users", true)
-				m.ExpectQuery(sqltest.Escape(columnsQuery)).
+				m.ExpectQuery(queryColumns).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
-+-------------+---------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
-| column_name | column_type   | column_comment | is_nullable | column_key | column_default | extra | character_set_name | collation_name |
-+-------------+---------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
-| d1          | decimal(10,2) |                | NO          |            | 10.20          |       | NULL               | NULL           |
-| d2          | decimal(10,0) |                | NO          |            | 10             |       | NULL               | NULL           |
-+-------------+---------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
++------------+--------------+---------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
+| table_name |  column_name | column_type   | column_comment | is_nullable | column_key | column_default | extra | character_set_name | collation_name |
++------------+--------------+---------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
+| users      |  d1          | decimal(10,2) |                | NO          |            | 10.20          |       | NULL               | NULL           |
+| users      |  d2          | decimal(10,0) |                | NO          |            | 10             |       | NULL               | NULL           |
++------------+-------------+---------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
 `))
 				m.noIndexes()
 				m.noFKs()
@@ -203,15 +230,15 @@ func TestDriver_InspectTable(t *testing.T) {
 			name: "float types",
 			before: func(m mock) {
 				m.tableExists("public", "users", true)
-				m.ExpectQuery(sqltest.Escape(columnsQuery)).
+				m.ExpectQuery(queryColumns).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
-+-------------+--------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
-| column_name | column_type  | column_comment | is_nullable | column_key | column_default | extra | character_set_name | collation_name |
-+-------------+--------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
-| float       | float        |                | NO          |            |                |       | NULL               | NULL           |
-| double      | double       |                | NO          |            |                |       | NULL               | NULL           |
-+-------------+--------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
++------------+--------------+--------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
+| table_name |  column_name | column_type  | column_comment | is_nullable | column_key | column_default | extra | character_set_name | collation_name |
++------------+--------------+--------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
+| users      |  float       | float        |                | NO          |            |                |       | NULL               | NULL           |
+| users      |  double      | double       |                | NO          |            |                |       | NULL               | NULL           |
++------------+-------------+--------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
 `))
 				m.noIndexes()
 				m.noFKs()
@@ -229,19 +256,19 @@ func TestDriver_InspectTable(t *testing.T) {
 			name: "binary types",
 			before: func(m mock) {
 				m.tableExists("public", "users", true)
-				m.ExpectQuery(sqltest.Escape(columnsQuery)).
+				m.ExpectQuery(queryColumns).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
-+-------------+---------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
-| column_name | column_type   | column_comment | is_nullable | column_key | column_default | extra | character_set_name | collation_name |
-+-------------+---------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
-| c1          | binary(20)    |                | NO          |            | NULL           |       | NULL               | NULL           |
-| c2          | varbinary(30) |                | NO          |            | NULL           |       | NULL               | NULL           |
-| c3          | tinyblob      |                | NO          |            | NULL           |       | NULL               | NULL           |
-| c4          | mediumblob    |                | NO          |            | NULL           |       | NULL               | NULL           |
-| c5          | blob          |                | NO          |            | NULL           |       | NULL               | NULL           |
-| c6          | longblob      |                | NO          |            | NULL           |       | NULL               | NULL           |
-+-------------+---------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
++------------+--------------+---------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
+| table_name |  column_name | column_type   | column_comment | is_nullable | column_key | column_default | extra | character_set_name | collation_name |
++------------+--------------+---------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
+| users      |  c1          | binary(20)    |                | NO          |            | NULL           |       | NULL               | NULL           |
+| users      |  c2          | varbinary(30) |                | NO          |            | NULL           |       | NULL               | NULL           |
+| users      |  c3          | tinyblob      |                | NO          |            | NULL           |       | NULL               | NULL           |
+| users      |  c4          | mediumblob    |                | NO          |            | NULL           |       | NULL               | NULL           |
+| users      |  c5          | blob          |                | NO          |            | NULL           |       | NULL               | NULL           |
+| users      |  c6          | longblob      |                | NO          |            | NULL           |       | NULL               | NULL           |
++------------+--------------+---------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
 `))
 				m.noIndexes()
 				m.noFKs()
@@ -263,19 +290,19 @@ func TestDriver_InspectTable(t *testing.T) {
 			name: "string types",
 			before: func(m mock) {
 				m.tableExists("public", "users", true)
-				m.ExpectQuery(sqltest.Escape(columnsQuery)).
+				m.ExpectQuery(queryColumns).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
-+-------------+---------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
-| column_name | column_type   | column_comment | is_nullable | column_key | column_default | extra | character_set_name | collation_name |
-+-------------+---------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
-| c1          | char(20)      |                | NO          |            | char           |       | NULL               | NULL           |
-| c2          | varchar(30)   |                | NO          |            | NULL           |       | NULL               | NULL           |
-| c3          | tinytext      |                | NO          |            | NULL           |       | NULL               | NULL           |
-| c4          | mediumtext    |                | NO          |            | NULL           |       | NULL               | NULL           |
-| c5          | text          |                | NO          |            | NULL           |       | NULL               | NULL           |
-| c6          | longtext      |                | NO          |            | NULL           |       | NULL               | NULL           |
-+-------------+---------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
++------------+-------------+---------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
+| table_name | column_name | column_type   | column_comment | is_nullable | column_key | column_default | extra | character_set_name | collation_name |
++------------+-------------+---------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
+| users      | c1          | char(20)      |                | NO          |            | char           |       | NULL               | NULL           |
+| users      | c2          | varchar(30)   |                | NO          |            | NULL           |       | NULL               | NULL           |
+| users      | c3          | tinytext      |                | NO          |            | NULL           |       | NULL               | NULL           |
+| users      | c4          | mediumtext    |                | NO          |            | NULL           |       | NULL               | NULL           |
+| users      | c5          | text          |                | NO          |            | NULL           |       | NULL               | NULL           |
+| users      | c6          | longtext      |                | NO          |            | NULL           |       | NULL               | NULL           |
++------------+-------------+---------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
 `))
 				m.noIndexes()
 				m.noFKs()
@@ -297,15 +324,15 @@ func TestDriver_InspectTable(t *testing.T) {
 			name: "enum type",
 			before: func(m mock) {
 				m.tableExists("public", "users", true)
-				m.ExpectQuery(sqltest.Escape(columnsQuery)).
+				m.ExpectQuery(queryColumns).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
-+-------------+---------------+----------------+-------------+------------+----------------+-------+--------------------+-------------------+
-| column_name | column_type   | column_comment | is_nullable | column_key | column_default | extra | character_set_name | collation_name    |
-+-------------+---------------+----------------+-------------+------------+----------------+-------+--------------------+-------------------+
-| c1          | enum('a','b') |                | NO          |            | NULL           |       | latin1             | latin1_swedish_ci |
-| c2          | enum('c','d') |                | NO          |            | d              |       | latin1             | latin1_swedish_ci |
-+-------------+---------------+----------------+-------------+------------+----------------+-------+--------------------+-------------------+
++------------+-------------+---------------+----------------+-------------+------------+----------------+-------+--------------------+-------------------+
+| table_name | column_name | column_type   | column_comment | is_nullable | column_key | column_default | extra | character_set_name | collation_name    |
++------------+-------------+---------------+----------------+-------------+------------+----------------+-------+--------------------+-------------------+
+| users      | c1          | enum('a','b') |                | NO          |            | NULL           |       | latin1             | latin1_swedish_ci |
+| users      | c2          | enum('c','d') |                | NO          |            | d              |       | latin1             | latin1_swedish_ci |
++------------+-------------+---------------+----------------+-------------+------------+----------------+-------+--------------------+-------------------+
 `))
 				m.noIndexes()
 				m.noFKs()
@@ -323,20 +350,20 @@ func TestDriver_InspectTable(t *testing.T) {
 			name: "time type",
 			before: func(m mock) {
 				m.tableExists("public", "users", true)
-				m.ExpectQuery(sqltest.Escape(columnsQuery)).
+				m.ExpectQuery(queryColumns).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
-+-------------+--------------+-------------------+-------------+------------+----------------------+--------------------------------+--------------------+----------------+
-| column_name | column_type  | column_comment    | is_nullable | column_key | column_default       | extra                          | character_set_name | collation_name |
-+-------------+--------------+-------------------+-------------+------------+----------------------+--------------------------------+--------------------+----------------+
-| c1          | date         |                   | NO          |            | NULL                 |                                | NULL               | NULL           |
-| c2          | datetime     |                   | NO          |            | NULL                 |                                | NULL               | NULL           |
-| c3          | time         |                   | NO          |            | NULL                 |                                | NULL               | NULL           |
-| c4          | timestamp    |                   | NO          |            | CURRENT_TIMESTAMP    | on update CURRENT_TIMESTAMP    | NULL               | NULL           |
-| c5          | year(4)      |                   | NO          |            | NULL                 |                                | NULL               | NULL           |
-| c6          | year         |                   | NO          |            | NULL                 |                                | NULL               | NULL           |
-| c7          | timestamp(6) |                   | NO          |            | CURRENT_TIMESTAMP(6) | on update CURRENT_TIMESTAMP(6) | NULL               | NULL           |
-+-------------+--------------+-------------------+-------------+------------+----------------------+--------------------------------+--------------------+----------------+
++------------+-------------+--------------+-------------------+-------------+------------+----------------------+--------------------------------+--------------------+----------------+
+| table_name | column_name | column_type  | column_comment    | is_nullable | column_key | column_default       | extra                          | character_set_name | collation_name |
++------------+-------------+--------------+-------------------+-------------+------------+----------------------+--------------------------------+--------------------+----------------+
+| users      | c1          | date         |                   | NO          |            | NULL                 |                                | NULL               | NULL           |
+| users      | c2          | datetime     |                   | NO          |            | NULL                 |                                | NULL               | NULL           |
+| users      | c3          | time         |                   | NO          |            | NULL                 |                                | NULL               | NULL           |
+| users      | c4          | timestamp    |                   | NO          |            | CURRENT_TIMESTAMP    | on update CURRENT_TIMESTAMP    | NULL               | NULL           |
+| users      | c5          | year(4)      |                   | NO          |            | NULL                 |                                | NULL               | NULL           |
+| users      | c6          | year         |                   | NO          |            | NULL                 |                                | NULL               | NULL           |
+| users      | c7          | timestamp(6) |                   | NO          |            | CURRENT_TIMESTAMP(6) | on update CURRENT_TIMESTAMP(6) | NULL               | NULL           |
++------------+--------------+-------------------+-------------+------------+----------------------+--------------------------------+--------------------+----------------+
 `))
 				m.noIndexes()
 				m.noFKs()
@@ -359,14 +386,14 @@ func TestDriver_InspectTable(t *testing.T) {
 			name: "json type",
 			before: func(m mock) {
 				m.tableExists("public", "users", true)
-				m.ExpectQuery(sqltest.Escape(columnsQuery)).
+				m.ExpectQuery(queryColumns).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
-+-------------+-------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
-| COLUMN_NAME | COLUMN_TYPE | COLUMN_COMMENT | IS_NULLABLE | COLUMN_KEY | COLUMN_DEFAULT | EXTRA | CHARACTER_SET_NAME | COLLATION_NAME |
-+-------------+-------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
-| c1          | json        |                | NO          |            | NULL           |       | NULL               | NULL           |
-+-------------+-------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
++------------+------------+-------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
+| TABLE_NAME |COLUMN_NAME | COLUMN_TYPE | COLUMN_COMMENT | IS_NULLABLE | COLUMN_KEY | COLUMN_DEFAULT | EXTRA | CHARACTER_SET_NAME | COLLATION_NAME |
++------------+------------+-------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
+| users      |c1          | json        |                | NO          |            | NULL           |       | NULL               | NULL           |
++------------+------------+-------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
 `))
 				m.noIndexes()
 				m.noFKs()
@@ -383,22 +410,22 @@ func TestDriver_InspectTable(t *testing.T) {
 			name: "spatial type",
 			before: func(m mock) {
 				m.tableExists("public", "users", true)
-				m.ExpectQuery(sqltest.Escape(columnsQuery)).
+				m.ExpectQuery(queryColumns).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
-+-------------+--------------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
-| column_name | column_type        | column_comment | is_nullable | column_key | column_default | extra | character_set_name | collation_name |
-+-------------+--------------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
-| c1          | point              |                | NO          |            | NULL           |       | NULL               | NULL           |
-| c2          | multipoint         |                | NO          |            | NULL           |       | NULL               | NULL           |
-| c3          | linestring         |                | NO          |            | NULL           |       | NULL               | NULL           |
-| c4          | multilinestring    |                | NO          |            | NULL           |       | NULL               | NULL           |
-| c5          | polygon            |                | NO          |            | NULL           |       | NULL               | NULL           |
-| c6          | multipolygon       |                | NO          |            | NULL           |       | NULL               | NULL           |
-| c7          | geometry           |                | NO          |            | NULL           |       | NULL               | NULL           |
-| c8          | geometrycollection |                | NO          |            | NULL           |       | NULL               | NULL           |
-| c9          | geomcollection     |                | NO          |            | NULL           |       | NULL               | NULL           |
-+-------------+--------------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
++------------+-------------+--------------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
+| table_name | column_name | column_type        | column_comment | is_nullable | column_key | column_default | extra | character_set_name | collation_name |
++------------+-------------+--------------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
+| users      | c1          | point              |                | NO          |            | NULL           |       | NULL               | NULL           |
+| users      | c2          | multipoint         |                | NO          |            | NULL           |       | NULL               | NULL           |
+| users      | c3          | linestring         |                | NO          |            | NULL           |       | NULL               | NULL           |
+| users      | c4          | multilinestring    |                | NO          |            | NULL           |       | NULL               | NULL           |
+| users      | c5          | polygon            |                | NO          |            | NULL           |       | NULL               | NULL           |
+| users      | c6          | multipolygon       |                | NO          |            | NULL           |       | NULL               | NULL           |
+| users      | c7          | geometry           |                | NO          |            | NULL           |       | NULL               | NULL           |
+| users      | c8          | geometrycollection |                | NO          |            | NULL           |       | NULL               | NULL           |
+| users      | c9          | geomcollection     |                | NO          |            | NULL           |       | NULL               | NULL           |
++------------+-------------+--------------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+
 `))
 				m.noIndexes()
 				m.noFKs()
@@ -423,34 +450,42 @@ func TestDriver_InspectTable(t *testing.T) {
 			name: "indexes",
 			before: func(m mock) {
 				m.tableExists("public", "users", true)
-				m.ExpectQuery(sqltest.Escape(columnsQuery)).
+				m.ExpectQuery(queryColumns).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
-+-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
-| COLUMN_NAME | COLUMN_TYPE  | COLUMN_COMMENT | IS_NULLABLE | COLUMN_KEY | COLUMN_DEFAULT | EXTRA          | CHARACTER_SET_NAME | COLLATION_NAME     |
-+-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
-| id          | int          |                | NO          | PRI        | NULL           |                | NULL               | NULL               |
-| nickname    | varchar(255) |                | NO          | UNI        | NULL           |                | utf8mb4            | utf8mb4_0900_ai_ci |
-| oid         | int          |                | NO          | MUL        | NULL           |                | NULL               | NULL               |
-| uid         | int          |                | NO          | MUL        | NULL           |                | NULL               | NULL               |
-+-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
++------------+-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
+| TABLE_NAME | COLUMN_NAME | COLUMN_TYPE  | COLUMN_COMMENT | IS_NULLABLE | COLUMN_KEY | COLUMN_DEFAULT | EXTRA          | CHARACTER_SET_NAME | COLLATION_NAME     |
++------------+-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
+| users      | id          | int          |                | NO          | PRI        | NULL           |                | NULL               | NULL               |
+| users      | nickname    | varchar(255) |                | NO          | UNI        | NULL           |                | utf8mb4            | utf8mb4_0900_ai_ci |
+| users      | oid         | int          |                | NO          | MUL        | NULL           |                | NULL               | NULL               |
+| users      | uid         | int          |                | NO          | MUL        | NULL           |                | NULL               | NULL               |
++------------+-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
 `))
-				m.ExpectQuery(sqltest.Escape(indexesExprQuery)).
+				m.ExpectQuery(queryIndexesExpr).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
-+--------------+-------------+------------+--------------+--------------+---------+--------------+------------+------------------+
-| INDEX_NAME   | COLUMN_NAME | NON_UNIQUE | SEQ_IN_INDEX | INDEX_TYPE   | DESC    | COMMENT      | SUB_PART   | EXPRESSION       |
-+--------------+-------------+------------+--------------+--------------+---------+--------------+------------+------------------+
-| nickname     | nickname    |          0 |            1 | BTREE        | nil     |              |        255 |      NULL        |
-| lower_nick   | NULL        |          1 |            1 | HASH         | 0       |              |       NULL | lower(nickname)  |
-| non_unique   | oid         |          1 |            1 | BTREE        | 0       |              |       NULL |      NULL        |
-| non_unique   | uid         |          1 |            2 | BTREE        | 0       |              |       NULL |      NULL        |
-| PRIMARY      | id          |          0 |            1 | BTREE        | 0       |              |       NULL |      NULL        |
-| unique_index | uid         |          0 |            1 | BTREE        | 1       |              |       NULL |      NULL        |
-| unique_index | oid         |          0 |            2 | BTREE        | 1       |              |       NULL |      NULL        |
-+--------------+-------------+------------+--------------+--------------+---------+--------------+------------+------------------+
++--------------+--------------+-------------+------------+--------------+--------------+---------+--------------+------------+------------------+
+| TABLE_NAME   | INDEX_NAME   | COLUMN_NAME | NON_UNIQUE | SEQ_IN_INDEX | INDEX_TYPE   | DESC    | COMMENT      | SUB_PART   | EXPRESSION       |
++--------------+--------------+-------------+------------+--------------+--------------+---------+--------------+------------+------------------+
+| users        | nickname     | nickname    |          0 |            1 | BTREE        | nil     |              |        255 |      NULL        |
+| users        | lower_nick   | NULL        |          1 |            1 | HASH         | 0       |              |       NULL | lower(nickname)  |
+| users        | non_unique   | oid         |          1 |            1 | BTREE        | 0       |              |       NULL |      NULL        |
+| users        | non_unique   | uid         |          1 |            2 | BTREE        | 0       |              |       NULL |      NULL        |
+| users        | PRIMARY      | id          |          0 |            1 | BTREE        | 0       |              |       NULL |      NULL        |
+| users        | unique_index | uid         |          0 |            1 | BTREE        | 1       |              |       NULL |      NULL        |
+| users        | unique_index | oid         |          0 |            2 | BTREE        | 1       |              |       NULL |      NULL        |
++--------------+--------------+-------------+------------+--------------+--------------+---------+--------------+------------+------------------+
 `))
 				m.noFKs()
+				m.ExpectQuery(sqltest.Escape("SHOW CREATE TABLE `public`.`users`")).
+					WillReturnRows(sqltest.Rows(`
++-------+---------------------------------------------------------------------------------------------------------------------------------------------+
+| Table | Create Table                                                                                                                                |
++-------+---------------------------------------------------------------------------------------------------------------------------------------------+
+| users | CREATE TABLE users (id bigint NOT NULL AUTO_INCREMENT) ENGINE=InnoDB AUTO_INCREMENT=55834574848 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
++-------+---------------------------------------------------------------------------------------------------------------------------------------------+
+`))
 			},
 			expect: func(require *require.Assertions, t *schema.Table, err error) {
 				require.NoError(err)
@@ -493,19 +528,19 @@ func TestDriver_InspectTable(t *testing.T) {
 			name: "fks",
 			before: func(m mock) {
 				m.tableExists("public", "users", true)
-				m.ExpectQuery(sqltest.Escape(columnsQuery)).
+				m.ExpectQuery(queryColumns).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
-+-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
-| COLUMN_NAME | COLUMN_TYPE  | COLUMN_COMMENT | IS_NULLABLE | COLUMN_KEY | COLUMN_DEFAULT | EXTRA          | CHARACTER_SET_NAME | COLLATION_NAME     |
-+-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
-| id          | int          |                | NO          | PRI        | NULL           |                | NULL               | NULL               |
-| oid         | int          |                | NO          | MUL        | NULL           |                | NULL               | NULL               |
-| uid         | int          |                | NO          | MUL        | NULL           |                | NULL               | NULL               |
-+-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
++------------+-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
+| TABLE_NAME | COLUMN_NAME | COLUMN_TYPE  | COLUMN_COMMENT | IS_NULLABLE | COLUMN_KEY | COLUMN_DEFAULT | EXTRA          | CHARACTER_SET_NAME | COLLATION_NAME     |
++------------+-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
+| users      | id          | int          |                | NO          | PRI        | NULL           |                | NULL               | NULL               |
+| users      | oid         | int          |                | NO          | MUL        | NULL           |                | NULL               | NULL               |
+| users      | uid         | int          |                | NO          | MUL        | NULL           |                | NULL               | NULL               |
++------------+-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
 `))
 				m.noIndexes()
-				m.ExpectQuery(sqltest.Escape(fksQuery)).
+				m.ExpectQuery(queryFKs).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
 +------------------+------------+-------------+--------------+-----------------------+------------------------+------------------------+-------------+-------------+
@@ -522,7 +557,7 @@ func TestDriver_InspectTable(t *testing.T) {
 				require.Equal("users", t.Name)
 				require.Equal("public", t.Schema.Name)
 				fks := []*schema.ForeignKey{
-					{Symbol: "multi_column", Table: t, OnUpdate: schema.NoAction, OnDelete: schema.Cascade, RefTable: &schema.Table{Name: "t1", Schema: &schema.Schema{Name: "public"}}, RefColumns: []*schema.Column{{Name: "gid"}, {Name: "xid"}}},
+					{Symbol: "multi_column", Table: t, OnUpdate: schema.NoAction, OnDelete: schema.Cascade, RefTable: &schema.Table{Name: "t1", Schema: t.Schema}, RefColumns: []*schema.Column{{Name: "gid"}, {Name: "xid"}}},
 					{Symbol: "self_reference", Table: t, OnUpdate: schema.NoAction, OnDelete: schema.Cascade, RefTable: t},
 				}
 				columns := []*schema.Column{
@@ -542,26 +577,34 @@ func TestDriver_InspectTable(t *testing.T) {
 			version: "8.0.16",
 			before: func(m mock) {
 				m.tableExists("public", "users", true)
-				m.ExpectQuery(sqltest.Escape(columnsQuery)).
+				m.ExpectQuery(queryColumns).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
-+-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
-| COLUMN_NAME | COLUMN_TYPE  | COLUMN_COMMENT | IS_NULLABLE | COLUMN_KEY | COLUMN_DEFAULT | EXTRA          | CHARACTER_SET_NAME | COLLATION_NAME     |
-+-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
-| id          | int          |                | NO          | PRI        | NULL           |                | NULL               | NULL               |
-| c1          | int          |                | NO          | MUL        | NULL           |                | NULL               | NULL               |
-+-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
++-------------+-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
+| TABLE_NAME  | COLUMN_NAME | COLUMN_TYPE  | COLUMN_COMMENT | IS_NULLABLE | COLUMN_KEY | COLUMN_DEFAULT | EXTRA          | CHARACTER_SET_NAME | COLLATION_NAME     |
++-------------+-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
+| users       | id          | int          |                | NO          | PRI        | NULL           |                | NULL               | NULL               |
+| users       | c1          | int          |                | NO          | MUL        | NULL           |                | NULL               | NULL               |
++-------------+-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
 `))
 				m.noIndexes()
 				m.noFKs()
-				m.ExpectQuery(sqltest.Escape(myChecksQuery)).
+				m.ExpectQuery(queryMyChecks).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
-+-------------------+-------------------------------------------+------------+
-| CONSTRAINT_NAME   | CHECK_CLAUSE                              |  ENFORCED  |
-+-------------------+-------------------------------------------+------------+
-| users_chk_1       | (` + "`c6`" + ` <>_latin1\'foo\\\'s\')    |  YES       |
-+-------------------+-------------------------------------------+------------+
++-------------------+-------------------+-------------------------------------------+------------+
+| TABLE_NAME        | CONSTRAINT_NAME   | CHECK_CLAUSE                              |  ENFORCED  |
++-------------------+-------------------+-------------------------------------------+------------+
+| users             | users_chk_1       | (` + "`c6`" + ` <>_latin1\'foo\\\'s\')    |  YES       |
++-------------------+-------------------+-------------------------------------------+------------+
+`))
+				m.ExpectQuery(sqltest.Escape("SHOW CREATE TABLE `public`.`users`")).
+					WillReturnRows(sqltest.Rows(`
++-------+------------------------+
+| Table | Create Table           |
++-------+------------------------+
+| users | CREATE TABLE users()   |
++-------+------------------------+
 `))
 			},
 			expect: func(require *require.Assertions, t *schema.Table, err error) {
@@ -573,7 +616,7 @@ func TestDriver_InspectTable(t *testing.T) {
 					{Name: "c1", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}},
 				}
 				require.EqualValues(columns, t.Columns)
-				require.EqualValues([]schema.Attr{&CreateStmt{S: "CREATE TABLE users()"}, &schema.Check{Name: "users_chk_1", Expr: "(`c6` <>_latin1\\'foo\\'s\\')"}}, t.Attrs)
+				require.EqualValues([]schema.Attr{&schema.Check{Name: "users_chk_1", Expr: "(`c6` <>_latin1\\'foo\\'s\\')"}, &CreateStmt{S: "CREATE TABLE users()"}}, t.Attrs)
 			},
 		},
 	}
@@ -595,7 +638,6 @@ func TestDriver_InspectTable(t *testing.T) {
 | public      | utf8mb4                    | utf8mb4_unicode_ci     |
 +-------------+----------------------------+------------------------+
 				`))
-			mk.tables("public", "users")
 			tt.before(mk)
 			drv, err := Open(db)
 			require.NoError(t, err)
@@ -669,51 +711,32 @@ func TestDriver_InspectSchema(t *testing.T) {
 +-------------+----------------------------+------------------------+
 | public      | utf8mb4                    | utf8mb4_unicode_ci     |
 +-------------+----------------------------+------------------------+
-				`))
+`))
 				m.tables("public", "users", "pets")
-				m.tableExistsInSchema("public", "users", true)
-				m.ExpectQuery(sqltest.Escape(columnsQuery)).
-					WithArgs("public", "users").
+				m.ExpectQuery(sqltest.Escape(fmt.Sprintf(columnsQuery, "?, ?"))).
+					WithArgs("public", "users", "pets").
 					WillReturnRows(sqltest.Rows(`
-+-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
-| COLUMN_NAME | COLUMN_TYPE  | COLUMN_COMMENT | IS_NULLABLE | COLUMN_KEY | COLUMN_DEFAULT | EXTRA          | CHARACTER_SET_NAME | COLLATION_NAME     |
-+-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
-| id          | int          |                | NO          | PRI        | NULL           |                | NULL               | NULL               |
-| spouse_id   | int          |                | YES         | NULL       | NULL           |                | NULL               | NULL               |
-+-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
-		`))
-				m.noIndexes()
-				m.ExpectQuery(sqltest.Escape(fksQuery)).
-					WithArgs("public", "users").
++-------------+-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
+| TABLE_NAME  | COLUMN_NAME | COLUMN_TYPE  | COLUMN_COMMENT | IS_NULLABLE | COLUMN_KEY | COLUMN_DEFAULT | EXTRA          | CHARACTER_SET_NAME | COLLATION_NAME     |
++-------------+-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
+| users       | id          | int          |                | NO          | PRI        | NULL           |                | NULL               | NULL               |
+| users       | spouse_id   | int          |                | YES         | NULL       | NULL           |                | NULL               | NULL               |
+| pets        | id          | int          |                | NO          | PRI        | NULL           |                | NULL               | NULL               |  
+| pets        | owner_id    | int          |                | YES         | NULL       | NULL           |                | NULL               | NULL               |  
++-------------+-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
+				`))
+				m.ExpectQuery(sqltest.Escape(fmt.Sprintf(indexesExprQuery, "?, ?"))).
+					WillReturnRows(sqlmock.NewRows([]string{"table_name", "index_name", "column_name", "non_unique", "key_part", "expression"}))
+				m.ExpectQuery(sqltest.Escape(fmt.Sprintf(fksQuery, "?, ?"))).
+					WithArgs("public", "users", "pets").
 					WillReturnRows(sqltest.Rows(`
 +------------------+------------+-------------+--------------+-----------------------+------------------------+------------------------+-------------+-------------+
 | CONSTRAINT_NAME  | TABLE_NAME | COLUMN_NAME | TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME | REFERENCED_SCHEMA_NAME | UPDATE_RULE | DELETE_RULE |
 +------------------+------------+-------------+--------------+-----------------------+------------------------+------------------------+-------------+-------------+
 | spouse_id        | users      | spouse_id   | public       | users                 | id                     | public                 | NO ACTION   | CASCADE     |
-+------------------+------------+-------------+--------------+-----------------------+------------------------+------------------------+-------------+-------------+
-		`))
-
-				m.tableExistsInSchema("public", "pets", true)
-				m.ExpectQuery(sqltest.Escape(columnsQuery)).
-					WithArgs("public", "pets").
-					WillReturnRows(sqltest.Rows(`
-+-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
-| COLUMN_NAME | COLUMN_TYPE  | COLUMN_COMMENT | IS_NULLABLE | COLUMN_KEY | COLUMN_DEFAULT | EXTRA          | CHARACTER_SET_NAME | COLLATION_NAME     |
-+-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
-| id          | int          |                | NO          | PRI        | NULL           |                | NULL               | NULL               |
-| owner_id    | int          |                | YES         | NULL       | NULL           |                | NULL               | NULL               |
-+-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
-		`))
-				m.noIndexes()
-				m.ExpectQuery(sqltest.Escape(fksQuery)).
-					WithArgs("public", "pets").
-					WillReturnRows(sqltest.Rows(`
-+------------------+------------+-------------+--------------+-----------------------+------------------------+------------------------+-------------+-------------+
-| CONSTRAINT_NAME  | TABLE_NAME | COLUMN_NAME | TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME | REFERENCED_SCHEMA_NAME | UPDATE_RULE | DELETE_RULE |
-+------------------+------------+-------------+--------------+-----------------------+------------------------+------------------------+-------------+-------------+
 | owner_id         | pets       | owner_id    | public       | users                 | id                     | public                 | NO ACTION   | CASCADE     |
 +------------------+------------+-------------+--------------+-----------------------+------------------------+------------------------+-------------+-------------+
-		`))
+				`))
 			},
 			expect: func(require *require.Assertions, s *schema.Schema, err error) {
 				require.NoError(err)
@@ -814,8 +837,9 @@ func TestDriver_Realm(t *testing.T) {
 | public      | utf8                       | utf8_general_ci        |
 +-------------+----------------------------+------------------------+
 `))
-	mk.tables("test")
-	mk.tables("public")
+	mk.ExpectQuery(sqltest.Escape(fmt.Sprintf(tablesQuery, "?, ?"))).
+		WithArgs("test", "public").
+		WillReturnRows(sqlmock.NewRows([]string{"schema", "table", "charset", "collate", "inc", "comment", "options"}))
 	realm, err = drv.InspectRealm(context.Background(), &schema.InspectRealmOption{Schemas: []string{"test", "public"}})
 	require.NoError(t, err)
 	require.EqualValues(t, func() *schema.Realm {
@@ -868,57 +892,31 @@ func (m mock) version(version string) {
 }
 
 func (m mock) noIndexes() {
-	m.ExpectQuery(sqltest.Escape(indexesExprQuery)).
-		WillReturnRows(sqlmock.NewRows([]string{"index_name", "column_name", "non_unique", "key_part", "expression"}))
+	m.ExpectQuery(queryIndexesExpr).
+		WillReturnRows(sqlmock.NewRows([]string{"table_name", "index_name", "column_name", "non_unique", "key_part", "expression"}))
 }
 
 func (m mock) noFKs() {
-	m.ExpectQuery(sqltest.Escape(fksQuery)).
-		WillReturnRows(sqlmock.NewRows([]string{"CONSTRAINT_NAME", "TABLE_NAME", "COLUMN_NAME", "REFERENCED_TABLE_NAME", "REFERENCED_COLUMN_NAME", "REFERENCED_TABLE_SCHEMA", "UPDATE_RULE", "DELETE_RULE"}))
-}
-
-func (m mock) tables(schema string, names ...string) {
-	rows := sqlmock.NewRows([]string{"table_name"})
-	for i := range names {
-		rows.AddRow(names[i])
-	}
-	m.ExpectQuery(sqltest.Escape(tablesQuery)).
-		WithArgs(schema).
-		WillReturnRows(rows)
+	m.ExpectQuery(queryFKs).
+		WillReturnRows(sqlmock.NewRows([]string{"TABLE_NAME", "CONSTRAINT_NAME", "TABLE_NAME", "COLUMN_NAME", "REFERENCED_TABLE_NAME", "REFERENCED_COLUMN_NAME", "REFERENCED_TABLE_SCHEMA", "UPDATE_RULE", "DELETE_RULE"}))
 }
 
 func (m mock) tableExists(schema, table string, exists bool) {
-	rows := sqlmock.NewRows([]string{"table_schema", "table_collation", "character_set", "auto_increment", "table_comment", "create_options"})
+	rows := sqlmock.NewRows([]string{"table_schema", "table_name", "table_collation", "character_set", "auto_increment", "table_comment", "create_options"})
 	if exists {
-		rows.AddRow(schema, nil, nil, nil, nil, nil)
+		rows.AddRow(schema, table, nil, nil, nil, nil, nil)
 	}
-	m.ExpectQuery(sqltest.Escape(tableSchemaQuery)).
-		WithArgs(table, schema).
+	m.ExpectQuery(queryTable).
+		WithArgs(schema).
 		WillReturnRows(rows)
-	m.ExpectQuery(sqltest.Escape(fmt.Sprintf("SHOW CREATE TABLE `%s`.`%s`", schema, table))).
-		WillReturnRows(sqltest.Rows(`
-+-------------+--------------------------------------+
-|    Table    | Create Table                         |
-+-------------+--------------------------------------+
-|` + table + `| CREATE TABLE ` + table + `()         |
-+-------------+--------------------------------------+
-`))
 }
 
-func (m mock) tableExistsInSchema(schema, table string, exists bool) {
-	rows := sqlmock.NewRows([]string{"table_schema", "table_collation", "character_set", "auto_increment", "table_comment", "create_options"})
-	if exists {
-		rows.AddRow(schema, nil, nil, nil, nil, nil)
+func (m mock) tables(schema string, tables ...string) {
+	rows := sqlmock.NewRows([]string{"schema", "table", "charset", "collate", "inc", "comment", "options"})
+	for _, t := range tables {
+		rows.AddRow(schema, t, nil, nil, nil, nil, nil)
 	}
-	m.ExpectQuery(sqltest.Escape(tableSchemaQuery)).
-		WithArgs(table, schema).
+	m.ExpectQuery(queryTable).
+		WithArgs(schema).
 		WillReturnRows(rows)
-	m.ExpectQuery(sqltest.Escape(fmt.Sprintf("SHOW CREATE TABLE `%s`.`%s`", schema, table))).
-		WillReturnRows(sqltest.Rows(`
-+-------------+--------------------------------------+
-|    Table    | Create Table                         |
-+-------------+--------------------------------------+
-|` + table + `| CREATE TABLE ` + table + `()         |
-+-------------+--------------------------------------+
-`))
 }