MySQL / MariaDB Schema Migration¶

-- Find all non-InnoDB tables
SELECT table_schema, table_name, engine
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND engine != 'InnoDB'
ORDER BY table_schema, table_name;

-- Convert MyISAM to InnoDB
ALTER TABLE mydb.my_table ENGINE = InnoDB;

-- Convert MariaDB Aria to InnoDB
ALTER TABLE mydb.my_table ENGINE = InnoDB;

-- Convert MEMORY to InnoDB (data will persist after restart)
-- Note: MEMORY tables lose data on restart anyway
ALTER TABLE mydb.my_table ENGINE = InnoDB;

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

-- PostgreSQL option 1: SERIAL (traditional)
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

-- PostgreSQL option 2: GENERATED ALWAYS (SQL standard, preferred)
CREATE TABLE users (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100)
);

-- PostgreSQL option 3: GENERATED BY DEFAULT (allows manual inserts)
CREATE TABLE users (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(100)
);

-- Reset sequence for each table with SERIAL/IDENTITY
SELECT setval(pg_get_serial_sequence('users', 'id'), (SELECT MAX(id) FROM users));

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

-- Azure SQL
CREATE TABLE users (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(100)
);

SET IDENTITY_INSERT users ON;
-- Insert data with explicit ID values
INSERT INTO users (id, name) VALUES (1, 'Alice'), (2, 'Bob');
SET IDENTITY_INSERT users OFF;

-- Reseed identity
DBCC CHECKIDENT('users', RESEED);

-- Database level
SELECT schema_name, default_character_set_name, default_collation_name
FROM information_schema.schemata
WHERE schema_name NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');

-- Table level
SELECT table_schema, table_name, table_collation
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND table_type = 'BASE TABLE'
ORDER BY table_schema, table_name;

-- Column level (only non-default)
SELECT table_schema, table_name, column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND character_set_name IS NOT NULL
ORDER BY table_schema, table_name, ordinal_position;

-- Convert database
ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- Convert all tables in a database (generate ALTER statements)
SELECT CONCAT('ALTER TABLE `', table_schema, '`.`', table_name,
              '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;')
FROM information_schema.tables
WHERE table_schema = 'mydb'
  AND table_type = 'BASE TABLE'
  AND table_collation != 'utf8mb4_0900_ai_ci';

-- Create PostgreSQL database with UTF-8 encoding
CREATE DATABASE mydb
  ENCODING = 'UTF8'
  LC_COLLATE = 'en_US.UTF-8'
  LC_CTYPE = 'en_US.UTF-8';

-- List all foreign keys
SELECT
    tc.table_schema, tc.table_name, tc.constraint_name,
    kcu.column_name, kcu.referenced_table_schema,
    kcu.referenced_table_name, kcu.referenced_column_name,
    rc.update_rule, rc.delete_rule
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
    AND tc.table_schema = kcu.table_schema
JOIN information_schema.referential_constraints rc
    ON tc.constraint_name = rc.constraint_name
    AND tc.table_schema = rc.constraint_schema
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_schema = 'mydb'
ORDER BY tc.table_name;

-- MySQL (source and Azure MySQL target)
SET FOREIGN_KEY_CHECKS = 0;
-- ... load data ...
SET FOREIGN_KEY_CHECKS = 1;

-- PostgreSQL target
-- Drop constraints, load data, re-add constraints
-- Or use: SET session_replication_role = 'replica'; (disables triggers and FK checks)
SET session_replication_role = 'replica';
-- ... load data ...
SET session_replication_role = 'origin';

-- Azure SQL target
ALTER TABLE child_table NOCHECK CONSTRAINT fk_name;
-- ... load data ...
ALTER TABLE child_table CHECK CONSTRAINT fk_name;

-- MySQL
CREATE FULLTEXT INDEX ft_articles ON articles(title, body);
SELECT * FROM articles WHERE MATCH(title, body) AGAINST('search term');

-- PostgreSQL equivalent
-- Step 1: Add tsvector column
ALTER TABLE articles ADD COLUMN search_vector tsvector;

-- Step 2: Populate tsvector
UPDATE articles SET search_vector =
    to_tsvector('english', COALESCE(title, '') || ' ' || COALESCE(body, ''));

-- Step 3: Create GIN index
CREATE INDEX ft_articles ON articles USING GIN(search_vector);

-- Step 4: Create trigger to maintain tsvector on INSERT/UPDATE
CREATE FUNCTION articles_search_trigger() RETURNS trigger AS $$
BEGIN
    NEW.search_vector := to_tsvector('english',
        COALESCE(NEW.title, '') || ' ' || COALESCE(NEW.body, ''));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tsvector_update BEFORE INSERT OR UPDATE
ON articles FOR EACH ROW EXECUTE FUNCTION articles_search_trigger();

-- Step 5: Query using tsquery
SELECT * FROM articles WHERE search_vector @@ to_tsquery('english', 'search & term');

-- MySQL RANGE partitioning
CREATE TABLE orders (
    id INT AUTO_INCREMENT,
    order_date DATE,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- PostgreSQL declarative partitioning equivalent
CREATE TABLE orders (
    id INTEGER GENERATED ALWAYS AS IDENTITY,
    order_date DATE NOT NULL,
    amount NUMERIC(10,2),
    PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2023 PARTITION OF orders
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE orders_2024 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE orders_2025 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE orders_future PARTITION OF orders
    FOR VALUES FROM ('2026-01-01') TO ('9999-12-31');

-- Create partition function
CREATE PARTITION FUNCTION pf_order_date (DATE)
AS RANGE RIGHT FOR VALUES ('2024-01-01', '2025-01-01', '2026-01-01');

-- Create partition scheme
CREATE PARTITION SCHEME ps_order_date
AS PARTITION pf_order_date ALL TO ([PRIMARY]);

-- Create partitioned table
CREATE TABLE orders (
    id INT IDENTITY(1,1),
    order_date DATE NOT NULL,
    amount DECIMAL(10,2),
    CONSTRAINT pk_orders PRIMARY KEY (id, order_date)
) ON ps_order_date(order_date);

-- MySQL view
CREATE VIEW active_customers AS
SELECT c.id, c.name, c.email, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id AND o.created_at > DATE_SUB(NOW(), INTERVAL 1 YEAR)
WHERE c.status = 'active'
GROUP BY c.id, c.name, c.email;

-- PostgreSQL equivalent
CREATE VIEW active_customers AS
SELECT c.id, c.name, c.email, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id AND o.created_at > NOW() - INTERVAL '1 year'
WHERE c.status = 'active'
GROUP BY c.id, c.name, c.email;

-- Azure SQL equivalent
CREATE VIEW active_customers AS
SELECT c.id, c.name, c.email, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id AND o.created_at > DATEADD(YEAR, -1, GETDATE())
WHERE c.status = 'active'
GROUP BY c.id, c.name, c.email;