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:

Example analysis of mysql variable usage [system variables, user variables]
Detailed explanation of mysql user variables and set statement examples

<<: VMware Workstation 12 Pro Linux installation tutorial

>>: VUE+Canvas implements the game of God of Wealth receiving ingots

Install CentOS7 in VMware (set static IP address) and install mySql database through docker container (super detailed tutorial)

A sophomore asked me how to install and configure...

Code analysis of user variables in mysql query statements

Demystifying the HTML 5 Working Draft

Docker data management and network communication usage

Analysis of the usage of replace and regexp for regular expression replacement in MySQL

CSS3 uses var() and calc() functions to achieve animation effects

How to purchase and initially build a server

Summary of MySQL Undo Log and Redo Log

Docker creates MySQL explanation

Problems and solutions for deploying Nginx+KeepAlived cluster dual-active architecture in VMware

Detailed explanation of the differences between js array find, some, filter, and reduce

JavaScript pre-analysis, object details

Recommend

Basic reference types of JavaScript advanced programming

Summary of MySQL's commonly used database and table sharding solutions

Why is it slow when using limit and offset paging scenarios?

Select web page drop-down list and div layer covering problem

Solution to the paging error problem of MySQL one-to-many association query

Summary of the installation process of MySql 8.0.11 and the problems encountered when linking with Navicat

A brief discussion on the optimization of MySQL paging for billions of data

Docker deploys mysql remote connection to solve 2003 problems

Implementation of automatic completion of Docker commands

How to achieve the maximum number of connections in mysql

Nodejs plug-in and usage summary

Install CentOS7 in VMware (set static IP address) and install mySql database through docker container (super detailed tutorial)

Running PostgreSQL in Docker and recommending several connection tools

How to write DROP TABLE in different databases

How to make spaces have the same width in IE and FF?