Code analysis of user variables in mysql query statements

In the previous article, we introduced the MySQL optimization summary - total number of queries. In this article, we will introduce another knowledge in query statements: code parsing of user variables.

Let’s start with the code

SELECT `notice`.`id` , `notice`.`fid` , `notice`.`has_read` , `notice`.`notice_time` , `notice`.`read_time` , `f`.`fnum` , `f`.`forg` , `f`.`fdst` , `f`.`actual_parking` AS `parking` , `f`.`scheduled_deptime` , `f`.`estimated_deptime` , `f`.`actual_deptime` , `f`.`scheduled_arrtime` , `f`.`estimated_arrtime` , `f`.`actual_arrtime` , `f`.`is_vip` , `f`.`aoc_notice`
FROM (
  select
  t.id, 
  t.fid, 
  t.has_read, 
  t.notice_time, 
  t.read_time
  from
  (
    select
    a.id, 
    a.fid, 
    a.has_read,
    a.notice_time, 
    a.read_time,
    @v_rownum := @v_rownum+1,
    if(@v_fid=a.fid,@v_rowid:=@v_rowid+1,@v_rowid:=1 ) as row_count,
    @v_fid:=a.fid
    from
    (
      SELECT
      id, 
      fid, 
      has_read, 
      notice_time, 
      read_time 
      FROM vkm_user_notice_flight
      where `notice_type` = 'process_update' and uid=82
      order by fid, notice_time desc
    ) a,
    (
      select @v_rownum:=0, @v_rowid:=0, @v_fid:=null
    ) b
  )
  where t.row_count = 1
) AS `notice`
LEFT JOIN `vkm_flight` AS `f` ON `notice`.`fid` = `f`.`id`
LEFT JOIN `vkm_parking` AS `parking` ON `f`.`actual_parking` = `parking`.`parking_num`

A friend sent me a SQL statement like this at work and I was confused at first and didn’t understand it at all! Because I am not very proficient in MySQL and can only do simple additions, deletions and modifications. In fact, the above code is written very directly.

SELECT `notice`.`id` , `notice`.`fid` , `notice`.`has_read` , `notice`.`notice_time` , `notice`.`read_time` , `f`.`fnum` , `f`.`forg` , `f`.`fdst` , `f`.`actual_parking` AS `parking` , `f`.`scheduled_deptime` , `f`.`estimated_deptime` , `f`.`actual_deptime` , `f`.`scheduled_arrtime` , `f`.`estimated_arrtime` , `f`.`actual_arrtime` , `f`.`is_vip` , `f`.`aoc_notice`
FROM `notice` LEFT JOIN `vkm_flight` AS `f` ON `notice`.`fid` = `f`.`id` LEFT JOIN `vkm_parking` AS `parking` ON `f`.`actual_parking` = `parking`.`parking_num`

But the query efficiency of the above code is really different! I don't understand the specific code above, and I'm asking for guidance, but I searched on Baidu and found one is the use of if in mysql and the other is the use of user variables in mysql

The user name can be set by set var value or in the above form @var:=val;

Use of if in mysql if(exp1,exp2,exp3) In if, if exp1 is true, then execute exp2, otherwise execute exp3;

Now looking at the above code may be much simpler! Based on the above code, I wrote a simple application example again

select id,fnum,forg,fdst,@v_rownum:=@v_rownum+1 from vkm_flight,(select @v_rownum:=0) b

This returns the value of @v_rownum

Summarize

The above is all the content of this article about the code analysis of user variables in MySQL query statements. I hope it will be helpful to everyone. Interested friends can continue to refer to this site: Examples of using or statements in MySQL, Detailed explanation of the meanings of N and M in the MySQL data type DECIMAL(N,M), etc. If you have any questions, you can leave a message at any time and the editor will reply to you in time. Thank you friends for supporting this site!

You may also be interested in: