database.yml
development:
prepared_statements: false
encoding: utf8mb4
socket: /tmp/mysql.sock
adapter: mysql2
/etc/mysql/my.cnf
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

PGPASSWORD=yourpass pg_dump discourse_development -h localhost   --quote-all-identifiers   --quote-all-identifiers   --inserts   --disable-dollar-quoting   --column-inserts   --exclude-table-data user_options   --exclude-table-data user_api_keys   --exclude-table-data  reviewable_histories   --exclude-table-data  reviewables   --exclude-table-data  notifications   --exclude-table-data site_settings   --exclude-table-data  reviewables    --no-acl   --no-owner   --format p   --data-only -f pgfile.sql

bundle exec rake db:seed:dump
MODELS=UserApiKey,UserOption,ReviewableHistory,
Reviewable,Notification,SiteSetting
EXCLUDE=[] IMPORT=true

# postgresql
SELECT DISTINCT ON (pr.user_id) pr.user_id, pr.post_id, pr.created_at granted_at
FROM post_revisions pr
JOIN badge_posts p on p.id = pr.post_id
WHERE p.wiki
AND NOT pr.hidden
AND (:backfill OR p.id IN (:post_ids))
# mysql
SELECT pr.user_id, MIN(pr.post_id) AS post_id, MIN(pr.created_at) AS granted_at
FROM post_revisions pr
JOIN badge_posts p on p.id = pr.post_id
WHERE p.wiki
AND NOT pr.hidden
AND (:backfill OR p.id IN (:post_ids))
GROUP BY pr.user_id

update users set updated_at = now() where id = 801 returning id,updated_at ;
id | updated_at
-----+---------------------------
801 | 2019-12-30 15:43:35.81969

update users set updated_at = now() where id = 801;
select id, updated_at from users where id = 801;
+-----+---------------------+
| id | updated_at |
+-----+---------------------+
| 801 | 2019-12-30 15:45:46 |
+-----+---------------------+

insert into category_users(user_id, category_id, notification_level) values(100,100,1) returning id, user_id, category_id;
id | user_id | category_id
----+---------+-------------
59 | 100 | 100
改成 MySQL 版本：
insert into category_users(user_id, category_id, notification_level) values(100,100,1);
select id, category_id, user_id from category_users where id = last_insert_id();
+----+-------------+---------+
| id | category_id | user_id |
+----+-------------+---------+
| 48 | 100 | 100 |
+----+-------------+---------+

ub_ids = records.map do |ub|
DB.exec(
"INSERT IGNORE INTO user_badges(badge_id, user_id, granted_at, granted_by_id, post_id)
VALUES (:badge_id, :user_id, :granted_at, :granted_by_id, :post_id)",
badge_id: badge.id,
user_id: ub.user_id,
granted_at: ub.granted_at,
granted_by_id: -1,
post_id: ub.post_id
)
DB.raw_connection.last_id
end
DB.query("SELECT id, user_id, granted_at FROM user_badges WHERE id IN (:ub_ids)", ub_ids: ub_ids)

# postgresql
DB.exec(<<~SQL, args)
INSERT INTO post_timings (topic_id, user_id, post_number, msecs)
SELECT :topic_id, :user_id, :post_number, :msecs
ON CONFLICT DO NOTHING
SQL
# MySQL
DB.exec(<<~SQL, args)
INSERT IGNORE INTO post_timings (topic_id, user_id, post_number, msecs)
SELECT :topic_id, :user_id, :post_number, :msecs
SQL

execute("create table one_row_table (id int)")
execute("insert into one_row_table values (1)")
MySQL使用：
# MySQL
select 1 from one_row_table where 1=2;

# MySQL
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

WITH RECURSIVE breadcrumb(id, level) AS (
SELECT 8543, 0
UNION
SELECT reply_id, level + 1
FROM post_replies AS r
JOIN breadcrumb AS b ON (r.post_id = b.id)
WHERE r.post_id <> r.reply_id
AND b.level < 1000
), breadcrumb_with_count AS (
SELECT
id,
level,
COUNT(*) AS count
FROM post_replies AS r
JOIN breadcrumb AS b ON (r.reply_id = b.id)
WHERE r.reply_id <> r.post_id
GROUP BY id, level
)
SELECT id, level
FROM breadcrumb_with_count
ORDER BY id
使用 MySQL 5.7 来兼容：
# MySQL
SELECT id, level FROM (
SELECT id, level, count(*) as count FROM (
SELECT reply_id AS id, length(@pv) - length((replace(@pv, ',', ''))) AS level
FROM (
SELECT * FROM post_replies ORDER BY post_id, reply_id) pr,
(SELECT @pv := 8543) init
WHERE find_in_set(post_id, @pv)
AND length(@pv := concat(@pv, ',', reply_id))
) tmp GROUP BY id, level
) tmp1
WHERE (count = 1)
ORDER BY id

WITH mods AS (
SELECT
id AS user_id,
username_lower AS username,
uploaded_avatar_id
FROM users u
WHERE u.moderator = 'true'
AND u.id > 0
),
time_read AS (
SELECT SUM(uv.time_read) AS time_read,
uv.user_id
FROM mods m
JOIN user_visits uv
ON m.user_id = uv.user_id
WHERE uv.visited_at >= '#{report.start_date}'
AND uv.visited_at <= '#{report.end_date}'
GROUP BY uv.user_id
),
flag_count AS (
WITH period_actions AS (
SELECT agreed_by_id,
disagreed_by_id
FROM post_actions
WHERE post_action_type_id IN (#{PostActionType.flag_types_without_custom.values.join(',')})
AND created_at >= '#{report.start_date}'
AND created_at <= '#{report.end_date}'
),
agreed_flags AS (
SELECT pa.agreed_by_id AS user_id,
COUNT(*) AS flag_count
FROM mods m
JOIN period_actions pa
ON pa.agreed_by_id = m.user_id
GROUP BY agreed_by_id
),
disagreed_flags AS (
SELECT pa.disagreed_by_id AS user_id,
COUNT(*) AS flag_count
FROM mods m
JOIN period_actions pa
ON pa.disagreed_by_id = m.user_id
GROUP BY disagreed_by_id
)

DB.exec(<<~SQL)
CREATE TEMPORARY TABLE IF NOT EXISTS mods AS (
SELECT
id AS user_id,
username_lower AS username,
uploaded_avatar_id
FROM users u
WHERE u.moderator = true
AND u.id > 0
)
SQL
DB.exec(<<~SQL)
CREATE TEMPORARY TABLE IF NOT EXISTS time_read AS (
SELECT SUM(uv.time_read) AS time_read,
uv.user_id
FROM mods m
JOIN user_visits uv
ON m.user_id = uv.user_id
WHERE uv.visited_at >= '#{report.start_date.to_s(:db)}'
AND uv.visited_at <= '#{report.end_date.to_s(:db)}'
GROUP BY uv.user_id
)
SQL

# postgresql
UPDATE employees
SET department_name = departments.name
FROM departments
WHERE employees.department_id = departments.id
MySQL 的 update 语句写法：
# MySQL
UPDATE employees
LEFT JOIN departments ON employees.department_id = departments.id
SET department_name = departments.name

# MySQL
update users set updated_at = now() where id in (
select id from users where id < 10
);
# You can't specify target table 'users' for update in FROM clause

# MySQL
update users set updated_at = now() where id in (
select id from (select * from users) u where id < 10
);

# MySQL
update users set updated_at = now() where id in (
select id from (select * from users) u where id < 10 limit 10
);
# MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

# MySQL
update users set updated_at = now() where id in (
select id from (
select id from (select * from users) u where id < 10 limit 10
) u1
);

# postgresql
WITH ranked_requests AS (
SELECT row_number() OVER (ORDER BY count DESC) as row_number, id
FROM web_crawler_requests
WHERE date = '#{1.day.ago.strftime("%Y-%m-%d")}'
)
DELETE FROM web_crawler_requests
WHERE id IN (
SELECT ranked_requests.id
FROM ranked_requests
WHERE row_number > 10
)
# MySQL
DELETE FROM web_crawler_requests
WHERE id IN (
SELECT ranked_requests.id
FROM (
SELECT @r := @r + 1 as row_number, id
FROM web_crawler_requests, (SELECT @r := 0) t
WHERE date = '#{1.day.ago.strftime("%Y-%m-%d")}'
ORDER BY count DESC
) ranked_requests
WHERE row_number > 10
)

# postgresql
create table tmp (id integer primary key, c1 varchar(10), c2 varchar(10));
insert into tmp values (1,2,3);
insert into tmp values (2,4,5);
select * from tmp;
id | c1 | c2
----+----+----
1 | 3 | 2
2 | 5 | 4
update tmp set c1=c2,c2=c1;
select * from tmp;
id | c1 | c2
----+----+----
1 | 3 | 2
2 | 5 | 4
# MySQL
create table tmp (id integer primary key, c1 varchar(10), c2 varchar(10));
insert into tmp values (1,2,3);
insert into tmp values (2,4,5);
select * from tmp;
+----+------+------+
| id | c1 | c2 |
+----+------+------+
| 1 | 2 | 3 |
| 2 | 4 | 5 |
+----+------+------+
update tmp set c1=c2,c2=c1;
select * from tmp;
+----+------+------+
| id | c1 | c2 |
+----+------+------+
| 1 | 3 | 3 |
| 2 | 5 | 5 |
+----+------+------+

t.virtual "virtual_parent_category_id", type: :string, as: "COALESCE(parent_category_id, '-1')", stored: true
t.index "virtual_parent_category_id, name", name: "unique_index_categories_on_name", unique: true

invalid: insert into t1 (i) select 1;
valid: insert into t1 (i) select 1 from dual;