Detailed explanation of mysql similar to oracle rownum writing example

rownum is written only in oracle, and rownum can be used in oracle to fetch the first data or limit the number of batch writes when writing data in batches

mysql takes the first data writing method

SELECT * FROM t order by id LIMIT 1;

oracle takes the first data writing method

SELECT * FROM t where rownum =1 order by id;

ok, the above is the comparison between mysql and oracle, but this is only one usage of rownum, and rownum can also be used to write data in batches

Write 10,000 pieces of data to t table in batches:

 insert into t(id,date) select sys_guid(),sysdate from dual connect by rownum<=10000;

oracle original writing:

select * from (select id,name from t) where rownum <![CDATA[<=]]> to_number(num);

mysql rewritten SQL:

SELECT 
 * 
FROM
 (SELECT 
  tb.*,
  @rownum := @rownum + 1 AS rownum 
 FROM
  (SELECT 
   id,
   NAME 
  FROM
   t) tb,
  (SELECT 
   @rownum := 0) r) AS t 
WHERE rownum <= CAST(num AS SIGNED INTEGER) ;

The above is all the knowledge points introduced this time. Thank you for your support to this site.