Use code parsing for user variables in mysql query statements, mysql statements

Use code parsing for user variables in mysql query statements, mysql statements

In the previous article, we introduced MySQL optimization Summary-Total number of queries. This article introduces another knowledge in the query statement: code parsing for user variables.

Run the code first.

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  ) t  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`

At work, my friends sent me an SQL statement like this, which I was not familiar with at first! Because I am not very familiar with mysql, I only need to add, delete, and modify it. In fact, the above code is very straightforward.

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`

However, the query efficiency of the above Code is too different! I have not understood the specific method of the above code, but I also requested guidance. However, if in mysql is used and user variables in mysql are used.

The User Name quantity can be set by using the set var value method or the above form @ var: = val;

In mysql, if (exp1, exp2, exp3) is used. if exp1 is true in if, exp2 is executed; otherwise, exp3 is executed;

Looking at the above Code, it may be much easier! Based on the above code, I wrote a simple application instance again.

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

In this way, the value of @ v_rownum is returned.

Summary

The above is all the content about using code to parse user variables in mysql query statements. I hope it will be helpful to you. If you are interested, you can continue to refer to this site: Examples of MySQL or statement usage, details of the meaning of N and M in MySQL Data Type DECIMAL (N, M), etc, if you have any questions, you can leave a message at any time. The editor will reply to you in a timely manner. Thank you for your support!