The method of querying Line number in MySQL by using function

This article mainly explains the "use function to achieve MySQL query line number method", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's train of thought slowly in-depth, together to study and learn "use function to achieve MySQL query line number method"!

Mysql5 uses functions to query line numbers in MySQL, which is similar to the use of variables.

The purpose of the following MySQL query line number procedure is to get a result set of the number of rows contained in the integer, similar to ROWNUM () in other DBMS. We need a user variable to hold the result after each call to rno (), so name it @ rno.

CREATEFUNCTIONrno () RETURNSINTBEGINSET@rno=@rno+1;RETURN@rno;END

We get the number of rows through the SELECT of the rno () method. Here is the result of the calling program:

Mysql > SET@rno=0;//QueryOK,0rowsaffected (0.00sec) mysql > SELECTrno (), S1 direction 2FROMt 5rowsinset + | rno () | S1 | S2 | +-+ | 1 | 1 | a | 2 | 2 | b | 3 | 3 | c | 4 | 4 | d | 5 | 5 | e | +-+ 5rowsinset (0.00sec)

The trick of setting @ rno to zero in SELECT is to use WHERE's evaluation function, which may be lost in future MySQL.

CREATEFUNCTIONrno_reset () RETURNSINTEGERBEGINSET@rno=0;RETURN1;END;SELECTrno (), s1meme s2FROMtWHERErnostatic reset () = 1bang /

Examples are as follows:

Mysql > usedb5Databasechangedmysql > delimiter//mysql > CREATEFUNCTIONrno ()-> RETURNSINT- > BEGIN- > SET@rno=@rno+1;- > RETURN@rno;- > END;//QueryOK,0rowsaffected (0.42sec) mysql > set@rno=0;- > / / QueryOK,0rowsaffected (0.00sec) mysql > selectrno (), s1fromtMaxima. | rno () | S1 | +-+-+ | 1 | 6 | 2 | 3 | 0 | 4 | 5 | 19 | 6 | 6 | 1 | 7 | 2 | 8 | 3 | 9 | 4 | 10 | 10 | 0 | 11 | 1 | 12 | 2 | 13 | 4 | +-+-- + 13rowsinset (0.06sec) mysql > CREATEFUNCTIONrno_reset ()-> RETURNSINTEGER- > BEGIN- SET@rno=0;- > RETURN1 -> END;//QueryOK,0rowsaffected (0.01sec) mysql > selectrno (), s1fromtwhererno_reset () = 1Hangxanghangmaowei + | rno () | S1 | +-+-+ | 1 | 6 | 2 | | 3 | 0 | 4 | 19 | 5 | 19 | 6 | 1 | | 7 | 2 | 8 | 3 | 9 | 4 | 10 | 0 | 11 | 1 | 12 | 2 | 13 | 4 | +-+-+ 13rowsinset (0.13sec)

Thank you for your reading, the above is the "use of functions to achieve MySQL query line number method" content, after the study of this article, I believe you have a deeper understanding of the use of functions to achieve MySQL query line number method, the specific use of the situation also needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!