mysql common syntax functions

1. Generate random time

SELECT DATE_ADD('2021-01-01 00:00:00',  INTERVAL  FLOOR(1 + (RAND() * 10800))   SECOND )

Generate random numbers


2. group by optimization

  • loose index

    Part of the scanned index needs to satisfy the left matching rule of the joint index.

  • compact index

    Range index scan or full scan. There is a gap in the index in group by, which is filled by the where constant.

  • understand

    When using it, there is no need to care about which one of the above, follow the following rules, and the efficiency will be fine.

  • condition

    • The fields after group by should be arranged in index order, and the query field is a subset of the fields after group by.
    • If the select column contains an index field that is not in the group by, the field must use an aggregate function
    • Extra in the execution plan is: Using index for group-by, Using index, Using index Using where

    If there is a table t1(c1,c2, c3,c4), the index is index(c1,c2, c3)

    SELECT c1, c2 FROM t1 GROUP BY c1, c2;
    SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
    --where The field can be any of the joint indexes
    SELECT c1, c2 FROM t1 WHERE c1 < 1 GROUP BY c1, c2;
    SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > 1 GROUP BY c1, c2;
    SELECT c2 FROM t1 WHERE c1 < 1 GROUP BY c1, c2;
    SELECT c1, c2 FROM t1 WHERE c3 = 1 GROUP BY c1, c2;
    --There are gaps in the index, fill the gaps and use the compact index
    SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;
    SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;

3. order by optimization

  • The query column is a subset of the order by field (joint index)

    select c1,c2 from t1 ORDER BY c1,c2;--Using index

  • The order by field is not a left prefix, and the where statement is a constant to make up for the lack of indexes

    Reason: c1 is a constant, and the queried c2 are all ordered. If the query range of c1 is small, the cost is lower than full scan. Can use index sorting

    select c1,c2 from t1 where c1=1 ORDER BY c2;--Using where; Using index
    select * from t1 where c1=1 ORDER BY c2;--is the only use select *Sortable scenes
  • index not used

    select * from t1 ORDER BY c1,c2;--Using filesort,Non-subsets will be returned to the table, the cost is high, and the index is abandoned
    select c1,c2 from t1 ORDER BY c1,c2 desc;--Using index; Using filesort,Inversion violates index order
    --Indexes are only used for where query, but can't solve sorting	
    select * from t1 where c1=1 ORDER BY c1,c3;--Using index; Using filesort,
    select * from t1 where c2=1 ORDER BY c1 ,c3;--Using where; Using filesort

5. Update if available, insert if not available

​ A unique key or unique index is required, and the following all support batches.

  • duplicate key

    In the return value, 1 means that the insertion is successful, 2 means that the update is successful, and 0 means that the value exists and the value before and after the update is the same. The return value does not represent the actual number of rows affected, it is just a sign.

    For example: if there are no following two data in the table, the return value is 2

    There are these two pieces of data in the table and the new value create_time is different from the old value, then return 2+2=4

    The create_time in the table is the same as the old value, and returns 0

    INSERT INTO `call_number` (
    	)ON DUPLICATE KEY UPDATE create_time=values(create_time),last_update_time=VALUES(last_update_time)
  • insert ignore

    If there is no operation, if there is no, insert it. But there is a problem with the sql value and it will be inserted as well. For example, if the pass value of create_time is a letter, it can still be inserted.

    INSERT ignore INTO `td_call` (
  • replace into

    If there is no insert, if there is delete, insert

    REPLACE INTO `td_call` (

6. String manipulation

  • LOCATE(substr,str)

    Find the position where the substring appears for the first time in the parent string, counting from 1, and return 0 if there is no

     select locate('365','');--7
     select locate('365111','');--0
  • position(substr in str)


  • INSTR(str,substr)

    The function returns the position of the first occurrence of a substring in a string. The INSTR() function returns zero (0) if the substring is not found in str. Default is case insensitive.

    similar to

    SELECT INSTR('MySQL INSTR', 'sql');--3,not case sensitive
    SELECT INSTR('MySQL INSTR', BINARY 'sql');--use BINARY case sensitive

    Efficiency issues, like >=INSTR. It seems that this function cannot be optimized like, and its function is similar to the above two.

  • LEFT (str, len)

    Intercept a string of length len from the left, and return str when len>length(str). Discard the string after len

    SELECT LEFT(12345678, 4) AS Left_Num;--1234
    SELECT LEFT(12345678, 20) AS Left_Num;--12345678
  • FIND_IN_SET(str,strlist)

    strlist must be a string separated by . If any parameter is NULL, return NULL, if str is not in strlist, return 0

    SELECT FIND_IN_SET('abc','abcd,abc');--2
    SELECT FIND_IN_SET('abc','abcd,abc,abc');--2
    SELECT FIND_IN_SET('abc','abcd');--0
    SELECT FIND_IN_SET('abc',null);--NULL

7,update select

  • inner join

    UPDATE t_task t
      t_task t
      INNER JOIN t_record r ON = r.object_id
      WHERE = 1
      ORDER BY r.app_date DESC limit 1
    ) AS temp
    ON = AND ='123' SET =2

Tags: Database MySQL SQL

Posted by jmfillman on Thu, 19 Jan 2023 08:31:38 +0300