如何在MySQL中模拟DB2的ROW_NUMBER() OVER()函数以迁移DDL

Hey there! Let's figure out how to get that DB2 view working in MySQL. The error about missing ROW_NUMBER() makes total sense—prior to MySQL 8.0, window functions like that weren't supported. Here's how to fix this based on your MySQL version:

1. For MySQL 8.0 or Newer #

Great news: MySQL 8.0 and above fully supports the ROW_NUMBER() window function, just like DB2. You only need to adjust the identifier quoting (MySQL uses backticks by default instead of double quotes) to avoid syntax errors. Here's the updated view creation statement:

CREATE VIEW `MY_PORTAL`.`MyView` AS
SELECT 
    ROW_NUMBER() OVER() AS id,
    b.id batch_id,
    b.name batch_name,
    b.description batch_desc,
    b.status batch_status,
    b.classname batch_classname,
    b.active batch_active,
    b.server batch_server,
    s.id scheduler_id,
    s.typeid scheduler_type_id,
    str.typename scheduler_type_name,
    s.days scheduler_days,
    s.hours scheduler_hours,
    s.minutes scheduler_minutes,
    s.seconds scheduler_seconds
FROM myDB.batches b
LEFT OUTER JOIN myDB.schedulers s ON s.batchid = b.id
LEFT OUTER JOIN myDB.scheduler_type_ref str ON str.typeid = s.typeid;

A quick note: If you prefer using double quotes for identifiers, you can enable the ANSI_QUOTES SQL mode, but backticks are more universally compatible across default MySQL setups.

2. For MySQL 5.7 or Older (No Window Function Support) #

If you're stuck on an older MySQL version, you can simulate ROW_NUMBER() using a user variable to increment a row counter. Here's how to rewrite the view:

CREATE VIEW `MY_PORTAL`.`MyView` AS
SELECT 
    @row_num := @row_num + 1 AS id,
    b.id batch_id,
    b.name batch_name,
    b.description batch_desc,
    b.status batch_status,
    b.classname batch_classname,
    b.active batch_active,
    b.server batch_server,
    s.id scheduler_id,
    s.typeid scheduler_type_id,
    str.typename scheduler_type_name,
    s.days scheduler_days,
    s.hours scheduler_hours,
    s.minutes scheduler_minutes,
    s.seconds scheduler_seconds
FROM myDB.batches b
LEFT OUTER JOIN myDB.schedulers s ON s.batchid = b.id
LEFT OUTER JOIN myDB.scheduler_type_ref str ON str.typeid = s.typeid
CROSS JOIN (SELECT @row_num := 0) AS init_counter;

Important Caveat #

Just like your original DB2 query, both approaches above don't specify an ORDER BY in the row number calculation. That means the row IDs will be assigned based on whatever order MySQL returns the rows (which can be inconsistent between runs). If you need stable, predictable row numbering, add an ORDER BY clause:

• For MySQL 8.0+: Add it inside the OVER() clause, e.g., ROW_NUMBER() OVER(ORDER BY b.id) AS id
• For older versions: Add an ORDER BY at the end of the query, e.g., ORDER BY b.id

Also, make sure the MY_PORTAL schema exists before creating the view—if not, run CREATE DATABASE IF NOT EXISTS MY_PORTAL; first.

内容的提问来源于stack exchange，提问作者Naxi