MySQL Development basic series of SQL Model

I. Overview

Unlike other databases, MySQL can run different SQL model, SQL model defines the SQL syntax supported by the MySQL application, data validation, etc., which makes it easier to use MySQL in different environments.
SQL model is commonly used to solve the following types of problems

(1) by setting up SQL mode, the data verification can be done with different rigor, and the data preparation is guaranteed effectively.
(2) To ensure that most SQL conforms to the standard SQL syntax by setting the SQL model to ANSI mode, so that when the application is migrated between different databases, there is no need to make large modifications to the business SQL.
(3) Before data migration between different databases, by setting up SQL Mode, you can make the data on MySQL easier to migrate to the target database.

1. Demo SQL Model Strict mode

Starting with MySQL 5.7, the default is strict mode, and MySQL begins to adhere strictly to the SQL 92 specification. Strict data validation is achieved by Strict_trans_tables strict mode. The error data cannot be inserted into the table, thus guaranteeing the accuracy of the data.

-- View SQL model mode  SELECT @ @sql_mode;

　　

--   The following SQL model can be set to strict mode set session Sql_mode='strict_trans_tables  ';

--   New Table set address to 10 lengths createtable  test_sqlmode (   VARCHAR(  )--  insert more than the length of INSERT intoVALUES(' 123456789123 ');

As you can see, the strict mode of exceeding data insertion will be an error. If not strict mode, the warning warning is reported and the characters are intercepted.

　　

2. SQL Mode combination mode

A composite pattern is similar to a relationship between roles and permissions. So when the actual application, only need to set a pattern combination, you can set a lot of atomic mode, greatly facilitate the user's work.

ANSI Mode	Relaxed mode, check the inserted data, if not conform to the definition type or length, the data type adjustment or truncation save, reported warning warning. Equivalent to Real_as_float, Pipes_as_concat, Ansi_quotes, Ignore_space, and ANSI Combination mode
Traditional mode	Strict mode, when inserting data into the MySQL database, the strict verification of data, to ensure that the error data can not be inserted, reported error errors. when used for a transaction, the transaction is rolled back. The traditional mode is equivalent to Strict_trans_tables, Strict_all_tables, No_zero_in_date, No_zero_date, Error_for_division_by_zero, traditional and no_auto_create_user combination mode
Strict_trans_tables mode	Strict mode, strict data validation, error data can not be inserted, reported error errors. For example: Do not allow illegal dates, nor allow values that exceed the field length to be inserted into the field, give an error instead of a warning for inserting an incorrect value

Two. How SQL model migration is used

If there is a need for data migration between MySQL and other heterogeneous databases, the database composition pattern provided in MySQL will help with the data migration process. Easier to import targets for exporting data.

During data migration, SQL mode can be set to No_table_options mode, which removes the "engine" keyword from show create TABLE and obtains a generic build-up script.

-- view tables with engine engines CREATE TABLE Test_sqlmode;

-- remove all tables from engine engines. Change to universal table- -  Modify the SQL modelSET session Sql_mode='no_table_ OPTIONS'

MySQL Development basic series of SQL Model