PostgreSQL から MySQL への変換: 移行ガイド

# Schema only, plain SQL text
pg_dump -s -f schema.sql mydb

# Data only, as INSERT statements (portable, but slower to load than COPY)
pg_dump -a --inserts -f data.sql mydb

CREATE SEQUENCE orders_id_seq;
CREATE TABLE orders (
    id INTEGER DEFAULT nextval('orders_id_seq') PRIMARY KEY
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY
);

-- PostgreSQL
SELECT first_name || ' ' || last_name FROM users;

-- MySQL
SELECT CONCAT(first_name, ' ', last_name) FROM users;

-- PostgreSQL: double quotes for identifiers, single quotes for strings
SELECT "first_name" FROM users WHERE status = 'active';

-- MySQL: backticks for identifiers, single quotes for strings
SELECT `first_name` FROM users WHERE status = 'active';

-- PostgreSQL
SELECT * FROM users WHERE name ILIKE '%alice%';

-- MySQL (case-insensitive by default with ci collations)
SELECT * FROM users WHERE name LIKE '%alice%';

-- PostgreSQL
INSERT INTO users (name) VALUES ('Alice') RETURNING id;

-- MySQL (no RETURNING)
INSERT INTO users (name) VALUES ('Alice');
SELECT LAST_INSERT_ID();

-- PostgreSQL
SELECT * FROM orders LIMIT 10 OFFSET 20;

-- MySQL (same syntax)
SELECT * FROM orders LIMIT 10 OFFSET 20;
-- or shorthand:
SELECT * FROM orders LIMIT 20, 10;  -- LIMIT offset, count

-- MySQL: a stored procedure with an OUT parameter
DELIMITER ;;
CREATE PROCEDURE total_orders(IN cust_id INT, OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM orders WHERE customer_id = cust_id;
END;;
DELIMITER ;

CALL total_orders(1, @t);
SELECT @t;