MySQL UPDATE multi table Association update

1.MySQL can UPDATE data based on multi table query. For the UPDATE operation of multiple tables, you need to be careful. It is recommended to use the SELECT statement to query and verify whether the updated data is consistent with your expectations before updating.

Build two tables, one for product Table, which is used to store product information, including product price field price；Another table is product_price Watch. Now I want to product_price Price field in table price Update to product Price field in the table price 80 of%. 

Generally, there are four methods of association update:
1.use UPDATE: UPDATE product p, product_price pp SET pp.price = p.price * 0.8 WHERE p.productid= pp.productId;
2. adopt INNER JOIN: UPDATE product p INNER JOIN product_price pp ON p.productid= pp.productid SET pp.price = p.price * 0.8;
3. adopt LEFT JOIN:  UPDATE product p LEFT JOIN product_price pp ON p.productid= pp.productid SET p.isdelete = 1 WHERE pp.productid IS NULL;
4.Through subquery: UPDATE product_price pp SET price=(SELECT price*0.8 FROM product WHERE productid = pp.productid);

For details, please see: http://c.biancheng.net/view/8286.html

How does MySQL handle invalid data values?

2. The basic principle of MySQL data processing is "garbage comes in and garbage goes out". Generally speaking, it will store what kind of data you pass to MySQL. If you don't verify the data when storing them, what you get when retrieving them is not necessarily what you expect.

There are several SQL modes that can throw errors when encountering "abnormal" values. If you are familiar with other database management systems, you will find that this behavior is very similar to other database management systems.

The following describes how MySQL handles abnormal data by default and the impact on data processing when various SQL modes are enabled.

    By default, MySQL The values outside the normal range and other values outside the normal range will be handled according to the following rules:
    1.For numeric columns or TIME Column, those values beyond the legal value range will be truncated to the nearest endpoint of the value range, and the resulting value will be stored.
    2.For division TIME For columns of other types other than columns, illegal values will be converted to "zero" values consistent with this type.
    3.For character string columns (excluding ENUM or SET)，An overly long string will be truncated to the maximum length of the column.
    4.to ENUM or SET When assigning a value to a type column, it needs to be assigned according to the list of legal values given in the column definition. If you assign a value that is not an enumeration member to ENUM Column,
    Then the value of the column becomes an empty string. If you assign the value of a substring containing non collection members to SET Column, then these strings will be cleaned up and the remaining members will be assigned to the column.

If the above conversion occurs during the execution of statements such as addition, deletion, modification and query, MySQL will give a warning message. After executing one of these statements, you can use the SHOW WARNINGS statement to view the contents of the warning message.

If you need to perform more stringent checks when inserting or updating data, you can enable one of the following two SQL modes:

  SET sql_mode = 'STRICT_ALL_TABLES' ;
SET sql_mode = 'STRICT_TRANS_TABLES';

For tables that support transactions, both modes are the same. If a value is found to be invalid or missing, an error is generated and the statement is aborted and rolled back as if nothing had happened.

For tables that do not support transactions, these two modes have the following effects.

1) For both modes, if a value is found to be invalid or missing when inserting or modifying the first row, an error will be generated and the statement will abort as if nothing had happened. This is similar to the behavior of the transaction table.
 
2) In a statement used to insert or modify multiple rows, if an error occurs in a row after the first row, some rows will be modified. These two modes determine whether the statement should stop executing or continue executing at this moment.
   1.stay STRICT_ALL_TABLES In mode, an error is thrown and the statement stops executing. Because many of the lines affected by this statement have been modified, this will cause a "partial update" problem.
   2.stay STRICT_TRANS_TABLES In mode, for non transaction tables, MySQL Will abort the execution of the statement. Only in this way can we achieve the effect of transaction table. This effect can be achieved only when an error occurs in the first line.
   If the error is on a later line, some lines will be modified. Because those changes cannot be undone for non transactional tables, the MySQL The statement will continue to be executed to avoid the problem of "partial update".
   It converts all invalid values to their closest legal value. For missing values, MySQL The column will be set to the implicit default value of its data type,

The input data can be checked more strictly through the following modes:

    1.ERROR_ FOR_ DIVISION_ BY_ ZERO: In strict mode, it prevents values from entering the database if they are divided by zero. If you are not in strict mode, a warning message is generated and inserted NULL. 
2.NO_ ZERO_ DATE: In strict mode, it prevents the "zero" date value from entering the database.
3.NO_ ZERO_ IN_ DATE: In strict mode, it prevents incomplete date values with month or day parts of zero from entering the database.

In short, the strict mode of MySQL is the strict verification of data by MySQL itself, such as format, length, type, etc. For example, for an integer field, we write a string type of data, and MySQL will not report an error in the non strict mode. If a field of type char or varchar is defined, no error will be reported when the written or updated data exceeds the defined length.

Although we will do data verification in the code, it is generally believed that non strict mode is not good for programming. Starting the strict mode of MySQL is also a test of our code from a certain program point of view. If we do not start the strict mode and do not encounter errors in the development process, there may be incompatibilities when we go online or migrate the code. Therefore, it is best to start the strict mode of MySQL in the development process.

    Can pass select @@sql_mode;Command to see whether the current mode is strict or non strict.

For example, if you want all storage engines to enable strict mode and check for divide by zero errors, you can set the SQL mode as follows:

    SET sql_mode 'STRICT_ALL_TABLES, ERROR_FOR_DIVISION_BY_ZERO' ;

If you want to enable strict mode and all the additional restrictions, the easiest way is to enable tradional mode:

 SET sql_ mode 'TRADITIONAL' ;

The meaning of TRADITIONAL mode is "enable strict mode. When inserting data into MySQL database, the data will be strictly verified to ensure that the wrong data cannot be inserted. When used for transaction table, the transaction will be rolled back".

The strict model can be selectively weakened in some aspects. If SQL allow is enabled_ INVALID_ Dates mode, MySQL will not perform a comprehensive check on the date part. On the contrary, it only requires the month value to be between 1 and 12 and the number of days to be between 1 and 31, that is, invalid values such as' 2000-02-30 'or' 2000-06-31 'are allowed.

Another way to stop errors is to use the IGNORE keyword in INSERT or UPDATE statements. In this way, statements that will cause errors due to invalid values will only lead to warnings. These options give you the flexibility to choose the correct validity check level for your application.

Fundamentals of SQL injection

3. Basic principle of SQL injection (super detailed): https://www.jianshu.com/p/078df7a35671

What is SQL injection and how to avoid it?

4.SQL Injection is a security vulnerability in the database layer of Web program. It is the most and simplest vulnerability in the website. The main reason is that the program does not judge and process the legitimacy of the user input data, so that the attacker can add additional SQL statements to the SQL statements defined in advance in the Web application, and realize illegal operations without the knowledge of the administrator, so as to deceive the database server to execute unauthorized arbitrary queries, so as to further obtain data information.

In short, SQL injection is to add SQL statements to the string entered by the user. If the check is ignored in the poorly designed program, these injected SQL statements will be run by the database server as normal SQL statements, and the attacker can execute unplanned commands or access unauthorized data.

SQL injection has become the biggest risk of Web applications in the Internet world. It is necessary to prevent it from development, testing, online and other links. The following describes the principle of SQL injection and some methods to avoid SQL injection.

Principle of SQL injection

The principles of SQL injection mainly include the following four points:

1)Malicious splicing query:
    We know, SQL Statement can query, insert, update and delete data, and semicolons are used to separate different commands. For example:
    
        SELECT * FROM users WHERE user_id = $user_id

    Among them, user_id Is an incoming parameter. If the value of the incoming parameter is "1234"; DELETE FROM users"，Then the final query statement will become:
    
        SELECT * FROM users WHERE user_id = 1234; DELETE FROM users

        If the above statement is executed, it will be deleted users All data in the table.
        
2)Executing illegal commands with comments:
    SQL You can insert comments in a statement. For example:
    
        SELECT COUNT(*) AS 'num' FROM game_score WHERE game_id=24411 AND version=$version

    If version Contains a malicious string'-1' OR 3 AND SLEEP(500)--，Then the final query statement will become:
    
        SELECT COUNT(*) AS 'num' FROM game_score WHERE game_id=24411 AND version='-1' OR 3 AND SLEEP(500)--

    The above malicious queries just want to exhaust system resources, SLEEP(500) Will lead to SQL The statement runs all the time. If malicious instructions are added to modify and delete data, it will cause greater damage.
    
3)Illegal parameter passed in:
    SQL The string parameters passed in the statement are enclosed in single quotation marks. If the string itself contains single quotation marks and is not processed, the original text may be tampered with SQL Statement. For example:
    
        SELECT * FROM user_name WHERE user_name = $user_name

    If user_name The value of the passed in parameter is G'chen，Then the final query statement will become:
    
        SELECT * FROM user_name WHERE user_name ='G'chen'

    Generally, the above statements will be executed incorrectly, and the risk of such statements is relatively small. Although there is no syntax error, it may occur maliciously SQL Statement and run it in a way you don't expect.
    
4)Add additional conditions:
    stay SQL Statement to change the execution behavior. The condition is generally a true value expression. For example:
    
        UPDATE users SET userpass='$userpass' WHERE user_id=$user_id;

     If user_id The malicious string "1234" was passed in OR TRUE"，So the final SQL The statement changes to:

         UPDATE users SET userpass= '123456' WHERE user_id=1234 OR TRUE;

        This will change the passwords of all users.

Avoid SQL injection

For SQL injection, we can take appropriate precautions to protect data security. Here are some ways to avoid SQL injection.

    1. Filter the input content and verify the string
    Filtering input content is to eliminate illegal characters in user input before data is submitted to the database. You can use the processing function provided by the programming language or your own processing function to filter, and you can also use regular expressions to match safe strings.
    
    If the value belongs to a specific type or has a specific format, it is spliced SQL The statement should be verified to verify its effectiveness. For example, for an incoming value, if it can be determined to be an integer, it needs to be verified on both the browser (client) and the server to determine whether it is an integer.
    
    2. Parameterized query
    Parameterized queries are currently considered prevention SQL Injection attack is the most effective method. Parametric query refers to the use of parameters where values or data need to be filled in when designing to connect with the database and access data( Parameter)To give value.
    
    MySQL The parameter format is“?"Character plus parameter name, as shown below:
    UPDATE myTable SET c1 = ?c1, c2 = ?c2, c3 = ?c3 WHERE c4 = ?c4
    
    In the case of parameterized query, the database server will not treat the content of the parameter as SQL Statement, but completed in the database SQL Statement is compiled before running with parameters. Therefore, even if the parameter contains destructive instructions, it will not be run by the database.
    
    3. Safety test and safety audit
    In addition to developing specifications, appropriate tools are also needed to ensure the security of code. We should review the code in the development process, use tools to scan in the testing process, and regularly scan security vulnerabilities after going online. Through the inspection of multiple links, it can generally be avoided SQL Injected.
    
    Some people think stored procedures can be avoided SQL Injection, stored procedures are used more in traditional industries, which is useful for permission control. However, if dynamic query is used in stored procedures, splicing is not allowed SQL，There will be security risks.
    
    The following is what can be avoided in the development process SQL Some methods of injection.
    1. Avoid using dynamic SQL
    Avoid putting the user's input data directly into the SQL Statements, it is better to use prepared statements and parameterized queries, which is more secure.
    2. Do not keep sensitive data in plain text
    Encrypt private data stored in the database/Confidential data, which can provide another level of protection to prevent attackers from successfully draining sensitive data.
    3. Restrict database permissions and privileges
    Set the functions of database users to the minimum requirements; This limits what an attacker can do when trying to gain access.
    
    4. Avoid displaying database errors directly to users
    An attacker can use these error messages to obtain information about the database.

Some programming frameworks are also helpful for writing safer code, because it provides some functions to process strings and methods to use query parameters. But again, you can still write unsafe SQL statements. Therefore, in the final analysis, we need to have good coding specifications and make full use of various methods such as parametric query, string processing and parameter verification to protect the security of database and program.

MySQL index

5. Index is a special database structure, which is composed of one or more columns in the data table. It can be used to quickly query the records with a specific value in the data table.

In MySQL, there are usually two ways to access row data of database tables:

1. Sequential access

    Sequential access is to scan the whole table in the table and traverse it row by row from beginning to end until the qualified target data is found in the disordered row data.

    The implementation of sequential access is relatively simple, but when there is a large amount of data in the table, the efficiency is very low, which affects the processing performance of the database.

2. Index access

Index access is a way to directly access the record rows in the table by traversing the index.

The premise of using this method is to establish an index on the table. After the index is created on the column, the position of the corresponding record row can be found directly according to the index on the column, so as to quickly find the data.
The index stores pointers to the specified column data values, which are sorted according to the specified sort order.

In short, without indexes, MySQL You must read the entire table from the first record until you find the relevant row.
The larger the table, the more time it takes to query the data. If the column queried in the table has an index, MySQL You can quickly reach a location to search for data files without having to view all the data, which will save a lot of time.

Advantages and disadvantages of index

Index has its obvious advantages and inevitable disadvantages.

advantage
The advantages of indexing are as follows:

1.By creating a unique index, you can ensure the uniqueness of each row of data in the database table.
2.You can give it to all MySQL Sets the index for the column type.
3.It can greatly speed up the query speed of data, which is the main reason for using index.
4.In terms of reference integrity of data, the connection between tables can be accelerated.
5.When using grouping and sorting clauses for data query, it can also significantly reduce the time of grouping and sorting in the query

shortcoming
Adding indexes also has many disadvantages, mainly as follows:

1.Creating and maintaining index groups takes time, and as the amount of data increases, so does the time.
2.Indexes need to occupy disk space. In addition to the data space occupied by data tables, each index also needs to occupy a certain amount of physical space. If there are a large number of indexes, the index file may reach the maximum file size faster than the data file.
3.When the data in the table is added, deleted and modified, the index should also be maintained dynamically, which reduces the speed of data maintenance.

When using index, we need to consider the advantages and disadvantages of index.

Indexing can improve query speed, but it will affect the speed of inserting records. Because when inserting records into an indexed table, the database system will sort according to the index, which reduces the speed of inserting records and has a more obvious impact on the speed of inserting a large number of records. In this case, the best way is to delete the index in the table first, then insert the data, and then create the index after the insertion is completed.

How to improve the efficiency of index and design a more efficient index

6. The design of the index can follow some existing principles. When creating the index, we should try to comply with these principles, so as to improve the use efficiency of the index and use the index more efficiently.

1. Select a unique index

The value of the uniqueness index is unique. You can use the index to determine a record more quickly. For example, the secondary number in the student table is a unique field.
Establishing a unique index for this field can quickly determine the information of a student. If you use a name, it may have the same name, thus reducing the query speed.

2. Index fields that often need sorting, grouping and union operations

Often need ORDER BY,GROUP BY,DISTINCT and UNION And other fields, sorting operation will waste a lot of time. If you index it, you can effectively avoid sorting operations.

3. Index fields that are often used as query criteria

If a field is often used as query criteria, the query speed of this field will affect the query speed of the whole table. Therefore, indexing such fields can improve the query speed of the whole table.

        Note: the fields with frequent query criteria are not necessarily the columns to be selected. In other words, the columns most suitable for indexing appear in WHERE A column in a clause, or a column specified in a join clause,
        Instead of appearing in SELECT Select the column in the list after the keyword.

4. Limit the number of indexes

The number of indexes is not "the more the better". Each index needs to occupy disk space. The more indexes, the more disk space needed. When modifying the contents of a table, the index must be updated and sometimes refactored. Therefore, the more indexes, the longer it takes to update the table.

If an index is rarely used or never used, it will unnecessarily slow down the modification of the table. In addition, MySQL When generating an execution plan, each index should be considered, which also takes time. Creating redundant indexes brings more work to query optimization.
Too many indexes may also cause MySQL Unable to select the best index to use.

5. Try to use indexes with less data

If the value of the index is very long, the speed of the query will be affected. For example, for a CHAR(100) The time required for full-text retrieval of type fields must be compared CHAR(10) Fields of type take more time.

6. It is better not to use indexes for tables with small amount of data

Because the data is small, the query may take less time than traversing the index, and the index may not produce optimization effect.

7. Try to use prefix to index

If the value of the index field is very long, it is best to use the prefix of the value to index. For example, TEXT and BLOG Type of fields, full-text retrieval will be a waste of time.
If only the first few characters of the field are retrieved, the retrieval speed can be improved.

8. Delete indexes that are no longer used or rarely used

After a large number of data in the table are updated, or the use of data is changed, some original indexes may no longer be needed. These indexes should be found and deleted regularly to reduce the impact of indexes on update operations.