Mysql tips [record at any time]

1. ########## how to obtain the total number of rows obtained from the last query ########

Mysql> select SQL _CALC_FOUND_ROWS * from _ table _ limit 0, 10;
Mysql> select found_rows ();

If the total number of records is 100, you can use the found_row function to obtain the total number of rows, because the SQL _CALC_FOUND_ROWS ID is added.

[Note]: found_rows () can only run once. To save the number of rows, You can append the global session variable.

Mysql> select found_rows () into @__ amount;
Mysql> select @__ amount;

2. ######## SQL Execution sequence ##########

(8) SELECT (9) DISTINCT (11) <TOP_specification> <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) group by <group_by_list>
(6) WITH {CUBE | ROLLUP}
(7) HAVING (10) order by <order_by_list>

3. ####### problems with inconsistent group by data #########

Because group by only takes the row sorted by default in the group, the following SQL statement is incorrect.

Select max (created_date) from abc group by name;

It is found that it is not the 'row' record with the largest created_date.

That is, although the maximum created_date value column can be obtained, the records of other columns are not in the same row of data.

There are two solutions

A. Sort first and then group

Select * from (
Select * from abc orderby created_date desc
) A groupby a. name

B. Very skillful in table join operations.

Select * from abc a where exists
(
Select 1 from abc where created_date> a. created_date
) Groupby a. name

4. ######### statements for retrieving the first N records of each data category ############

The following statement is used to obtain the first five rows. It is also applicable to table join operations on different tables.

Select * from 'table' a where
5> = (select count (*) from 'table' where cid = a. cid and ordering> a. ordering)
Orderby a. cid, a. ordering desc;

For example, the first two comments

select a.*,c.* from article a left join comments c on a.id=c.a_id where 2 > (select count(*) from comments where a_id=c.a_id and comment_date > c.comment_date) ;

5. ######### shielding duplicate key errors ###############

This is very likely to be used when exporting the imported data.

Insertinto module (module_id, module_pid, module_name) values (, 'abc'), (, 'new module ')
On DUPLICATE key
Update module_name = module_name

6. ######## order by rand ###############

The specific efficiency still needs to be verified when detecting a foreigner site.

Assume that order by rand () limit 10 is required.

SELECT * FROM (
SELECT @ cnt: = COUNT (*) + 1,
@ Lim: = 10
FROM table name
) Vars STRAIGHT_JOIN (
SELECT r .*,
@ Lim: =@ lim-1
FROM table name r
WHERE (@ cnt: = @ cnt-1)
And rand () <@ lim/@ cnt
) I