Setting the file. One moment.
Subchapter 63.29
references/mysql-migrations/full-example.mdMarkdown7 KBView on GitHub
End-to-end example migrating a complete MySQL CREATE TABLE to DSQL.
MUST read type-mapping.md first for data type mappings and the CRITICAL Destructive Operations Warning. for DDL operation patterns.
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
tenant_id INT NOT NULL,
name VARCHAR(255) NOT NULL,
description MEDIUMTEXT,
price DECIMAL(10,2) NOT NULL,
category ENUM('electronics', 'clothing', 'food', 'other') DEFAULT 'other',
tags SET('sale', 'new', 'featured'),
metadata JSON,
stock INT UNSIGNED DEFAULT 0,
is_active TINYINT(1) DEFAULT 1,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (tenant_id) REFERENCES tenants(id),
INDEX idx_tenant (tenant_id),
INDEX idx_category (category),
FULLTEXT INDEX idx_name_desc (name, description)
) ENGINE=InnoDB;-- Step 1: Create table (one DDL per transaction)
CREATE TABLE products (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id VARCHAR(255) NOT NULL,
name VARCHAR(255) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL,
category VARCHAR(255) DEFAULT 'other' CHECK (category IN ('electronics', 'clothing', 'food', 'other')),
tags TEXT,
metadata TEXT,
stock INTEGER DEFAULT 0 CHECK (stock >= 0),
is_active BOOLEAN DEFAULT true,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Step 2: Create indexes (each in separate transaction, MUST use ASYNC)
CREATE INDEX ASYNC idx_products_tenant ON products(tenant_id);
CREATE INDEX ASYNC idx_products_category ON products(tenant_id, category);
-- MUST implement text search at application layer for FULLTEXT index equivalent| MySQL Feature | DSQL Decision |
|---|---|
AUTO_INCREMENT | UUID with gen_random_uuid(), or IDENTITY column with CACHE, or SEQUENCE (see AUTO_INCREMENT Migration) |
INT tenant_id | VARCHAR(255) for multi-tenant pattern |
MEDIUMTEXT | TEXT |
ENUM(...) | VARCHAR(255) with CHECK constraint |
SET(...) | TEXT (comma-separated) |
JSON | TEXT (JSON.stringify) |
UNSIGNED | CHECK (col >= 0) |
TINYINT(1) | BOOLEAN |
DATETIME | TIMESTAMP |
ON UPDATE CURRENT_TIMESTAMP | Application-layer SET updated_at = CURRENT_TIMESTAMP |
FOREIGN KEY | Application-layer referential integrity |
INDEX | CREATE INDEX ASYNC |
FULLTEXT INDEX | Application-layer text search |
ENGINE=InnoDB | MUST omit |
gen_random_uuid() (preferred for distributed workloads), IDENTITY column with GENERATED AS IDENTITY (CACHE ...), or explicit SEQUENCE. When choosing integer auto-increment, ALWAYS use GENERATED AS IDENTITY syntax (not SERIAL). See AUTO_INCREMENT Migration.