MySQL foundation FAQ collection

1. General rules for setting primary keys in sql?

   1. Do not update the value of the primary key column (the column to set the primary key cannot be updated)
   2. Do not reuse the value of the primary key column (the primary key value must be unique in the whole primary key column)
   3. Do not use the value that may change in the primary key column (the primary key column cannot have a binding update relationship with other columns)

2. Cannot partially use the distinct keyword?

   1. The distinct keyword applies to all columns, not just the columns that precede it

      (distinct keyword distinguishes rows of query results with granularity. If a row of data is displayed differently, only one row of data is displayed if it is the same.)

      mysql> SELECT DISTINCT student_id ,student_name,student_birthday FROM student;
      +------------+--------------+------------------+
      | student_id | student_name | student_birthday |
      +------------+--------------+------------------+
      |          1 | java         | 2021-11-01       |
      |          2 | python       | 2021-11-01       |
      |          3 | c            | 2021-11-01       |
      |          4 | java         | 2021-11-02       |
      |          5 | java         | 2021-11-02       |
      +------------+--------------+------------------+
      5 rows in set (0.00 sec)
      
      mysql> SELECT DISTINCT student_name,student_birthday FROM student;
      +--------------+------------------+
      | student_name | student_birthday |
      +--------------+------------------+
      | java         | 2021-11-01       |
      | python       | 2021-11-01       |
      | c            | 2021-11-01       |
      | java         | 2021-11-02       |
      +--------------+------------------+
      4 rows in set (0.00 sec)
      

3. limit 1,1 actual representation?

   1. limit 1,1 indicates that 1 line is displayed starting from line 2, and limit retrieves the first line 0
   2. After Mysql5.0, limit 4 offset 3 means: starting from line 3, 4 lines are displayed

4. The efficiency of exist and not exist is higher than that of in and not in

5. like '1000' and regexp '1000'

   1. like '1000' matches the entire column. Even if the column contains 1000, the result will not be returned
   2. When regexp '1000' matches, the whole column is matched. If the column contains 1000, the result is returned

6. Most DBMS splicing uses + or 𞓜, and MySQL is implemented using the Concat(A,B,C...) function

7. The DATEDIFF(NOW(),'1995-12-01 ') function calculates the number of days between the specified date and the present

8. STR_ TO_ The date ('1995,12,01 ','% y,% m,% d ') function converts the specified string to date format

9. Count (1) means to add a column of 1 to each row based on the query result, and calculate the number of 1 to achieve the statistical effect

10. When using group by, except for aggregate calculation, each column in the select statement must be given in the group by clause

11. where is used for filtering outside the group after grouping, and having is used for filtering inside the group during grouping

12. union is used to combine the same return columns and remove the duplicate values after combination. union all does not remove the duplicate values after combination

13. MyISAM engine supports full-text search (for understanding only)

    1. code

       -- Build table
       CREATE TABLE class(
           class_id INT,
           class_name VARCHAR(20),
           PRIMARY KEY (class_id),
           FULLTEXT(class_name)	-- Specify the index columns to be built for full-text search
       )ENGINE=MYISAM;	
       
       --Use full text search
       SELECT class_name FROM class WHERE MATCH(class_name) AGAINST('p1');
       

14. The keyword low can be added in the middle of insert into_ Priority to reduce the priority of inserting data and ensure the select priority

15. The statement in insert select does not require the returned column names to be consistent, but requires the returned query results to be consistent with the insertion order

16. When the update statement updates multiple rows, if an error occurs in one row, the overall update fails. Ignore in the update ignore tablename can skip the failure and continue to update

17. Common usage of alter table

    1. Add a column to the table

       ALTER TABLE student ADD remark VARCHAR(20);
       

    2. Modify column names for tables

       ALTER TABLE student CHANGE remark intro varchar(30);
       

    3. Delete column

       ALTER TABLE student DROP intro;
       

    4. Modify column type

       ALTER TABLE student MODIFY remark VARCHAR(100);
       

18. When SQL clients write stored procedures, they need to use the DELIMITER command to set the terminator

    DELIMITER // --Set operator
    CREATE PROCEDURE query_student_info_arg(
    	OUT student_id INT,
    	OUT class_id INT,
    	OUT school_id INT
    )
    BEGIN 
    	SELECT MAX(student.`student_id`) INTO student_id FROM student ;
    	SELECT MAX(class.`class_id`) INTO class_id FROM class ;
    	SELECT MAX(school.`school_id`) INTO school_id FROM school ;
    END//
    DELIMITER ; -- Setting the operator back to ;
    

19. When viewing the execution plan with the EXPLAIN command, the execution efficiency of the type column is from the best to the worst:

    system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
    

20. When viewing the execution plan with the EXPLAIN command, the important values and optimization schemes of the Extra column are:

    distinct: once mysql If you find a row that matches the union of rows, you will no longer search
    Using index: This occurs when the requested columns of the table are all part of the same index. The returned column data only uses the information in the index, and does not access the row records in the table. It is the performance of high performance.
    Using where: mysql The server will filter the rows after the storage engine retrieves them. That is, read the whole line of data first, and then press where Check the conditions, leave if they meet the requirements, and discard if they do not meet the requirements.
    Using temporary: mysql You need to create a temporary table to process the query. In this case, it is generally necessary to optimize. The first thing is to think of using index to optimize.
    Using filesort: mysql The results are sorted using an external index instead of reading rows from the table in index order. here mysql It will browse all qualified records according to the join type, save the sorting keyword and row pointer, then sort the keyword and retrieve the row information in order. In this case, it is generally necessary to consider using indexes for optimization.
    

MySQL database in Alibaba coding protocol

Table building protocol

1. [force] a field expressing the yes / no concept. You must use is_xxx. The data type is unsigned tinyint
   (1 means yes, 0 means no).
2. [mandatory] table names and field names must use lowercase letters or numbers. It is forbidden to start with numbers and only two underscores in the middle
   A number appears. The modification of database field names is very expensive because pre publishing is not possible, so field names need to be carefully considered.
3. [mandatory] plural nouns are not used in table names.
4. [mandatory] disable reserved words, such as desc, range, match, delayed, etc. Please refer to the official reserved words of MySQL.
5. [mandatory] the primary key index name is pk_ Field name; The unique index name is uk_ Field name; The normal index name is idx_ Field name.
6. [mandatory] decimal type is decimal, and float and double are prohibited.
7. [mandatory] if the stored strings are almost equal in length, use char fixed length string type.
8. [mandatory] varchar is a variable length string. No storage space is allocated in advance. The length should not exceed 5000. If the storage length
   If it is greater than this value, define the field type as text, separate a table and use the primary key to correspond, so as to avoid affecting the index efficiency of other fields
   Rate.
9. Three mandatory fields in the [mandatory] Table: id, gmt_create, gmt_modified.
10. [recommended] the naming of the table should preferably follow the "function of business name table".
11. [recommended] the database name shall be consistent with the application name as far as possible.
12. [recommended] if the meaning of the field is modified or the status indicated by the field is added, the field notes need to be updated in time.
13. The [recommended] field allows appropriate redundancy to improve query performance, but data consistency must be considered. Redundant fields shall follow:
    1) Not a frequently modified field.
    2) A field that is not a unique index.
    3) It is not a varchar super long field, let alone a text field.
14. [recommended] only when the number of rows in a single table exceeds 5 million or the capacity of a single table exceeds 2GB, it is recommended to separate the database and table.
15. [reference] appropriate character storage length not only saves database table space and index storage, but also improves retrieval efficiency
    Speed.

Index protocol

1. [mandatory] fields with unique business characteristics, even combined fields, must have unique indexes.
2. [mandatory] join is prohibited for more than three tables. The data types of the fields that need to be joined are absolutely consistent; For multi table Association query,
   Ensure that the associated fields need to have indexes
3. [mandatory] when creating an index on a varchar field, you must specify the index length. It is not necessary to create an index on all fields. According to
   The actual text discrimination determines the index length.
4. [mandatory] page search is strictly prohibited from left blur or full blur. If necessary, please go to the search engine to solve it.
5. [recommended] if there is an order by scenario, please pay attention to the order of the index. The last field of order by is the composite index
   Part of the index and placed at the end of the index combination order to avoid file_sort affects query performance.
6. [recommended] use the overlay index to query and avoid returning to the table.
7. [recommended] use delay association or sub query to optimize the super multi paging scenario.
8. [recommended] the goal of SQL performance optimization: at least reach the range level. It is required to be ref level. If it can be consts, it is best.
9. [recommendation] when building a composite index, the one with the highest discrimination is on the far left.
10. [recommended] prevent implicit conversion caused by different field types, resulting in index failure.

Source of some notes of the article: MySQL must know by Ben Forta, Java Development Manual (Taishan Edition) by Alibaba

Other address of post blog:
[STR_TO_DATE function conversion date format description]: https://www.cnblogs.com/feiwenstyle/p/9531571.html "STR_TO_DATE function conversion date format description"
[mysql explain command description and optimization scheme]: https://cloud.tencent.com/developer/article/1093229 "mysql explain"