Detailed example of mysql similar to oracle rownum writing

Rownum is a unique way of writing in Oracle. In Oracle, rownum can be used to retrieve the first piece of data or to limit the number of batches to be written when writing data in batches.

How to write the first data in mysql

SELECT * FROM t order by id LIMIT 1;

How to write the first data in oracle

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

OK, the above is a comparison of the writing methods of MySQL and Oracle to get the first data, but this is only one usage of rownum. Rownum can also be used to write data in batches.

Write 10,000 records to table t 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);

SQL rewritten by mysql:

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 of 123WORDPRESS.COM.