一.概述 與其它資料庫不同，mysql 可以運行不同的sql model 下， sql model 定義了mysql應用支持的sql語法，數據校驗等，這樣更容易在不同的環境中使用mysql。 sql model 常用來解決下麵幾類問題 (1) 通過設置sql mode, 可以完成不同嚴格程度的數據校 ...
一.概述
與其它資料庫不同，mysql 可以運行不同的sql model 下， sql model 定義了mysql應用支持的sql語法，數據校驗等，這樣更容易在不同的環境中使用mysql。
sql model 常用來解決下麵幾類問題
(1) 通過設置sql mode, 可以完成不同嚴格程度的數據校驗，有效地保障數據準備性。
(2) 通過設置sql model 為ansi 模式，來保證大多數sql符合標準的sql語法，這樣應用在不同資料庫之間進行遷移時，則不需要對業務sql 進行較大的修改。
(3) 在不同資料庫之間進行數據遷移之前，通過設置SQL Mode 可以使MySQL 上的數據更方便地遷移到目標資料庫中。
1. 演示sql model 嚴格模式
從mysql 5.7 開始, 預設是嚴格模式, mysql 開始嚴格遵從SQL 92規範。通過strict_trans_tables嚴格模式實現了數據的嚴格校驗。 使錯誤數據不能插入表中， 從而保證了數據的準確性。
-- 查看sql model 模式 SELECT @@sql_mode;
-- 下麵可將sql model設置為嚴格模式 set session sql_mode='STRICT_TRANS_TABLES';
-- 新建表設置address為10個長度 CREATE TABLE test_sqlmode ( address VARCHAR(10) ) -- 插入超過長度 INSERT INTO test_sqlmode VALUES('123456789123');
通過下圖可以看出， 嚴格模式下超出數據 插入將報錯。 如果不是嚴格模式，則會報warning 警告 並截取字元。
2. sql mode 組合模式
組合模式類似於角色和許可權的關係。這樣當實際應用時，只需要設置一個模式組合，就可以設置很多的原子模式，大大方便了用戶的工作。
|
ANSI模式 |
寬鬆模式，對插入數據進行校驗，如果不符合定義類型或長度，對數據類型調整或截斷保存，報warning警告。 等同於REAL_AS_FLOAT、PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE 和ANSI 組合模式 |
|
traditional 模式 |
嚴格模式，當向mysql資料庫插入數據時，進行數據的嚴格校驗，保證錯誤數據不能插入，報error錯誤。用於事務時，會進行事務的回滾。 TRADITIONAL 模式等同於STRICT_TRANS_TABLES、STRICT_ALL_TABLES、 NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、TRADITIONAL 和NO_AUTO_CREATE_USER 組合模式 |
|
STRICT_TRANS_TABLES模式 |
嚴格模式，進行數據的嚴格校驗，錯誤數據不能插入，報error錯誤。 例如:不允許非法日期，也不允許超過欄位長度的值插入欄位中，對於插入不正確的值給出錯誤而不是警告 |
二. sql model 遷移如何使用
如果mysql 與其它異構資料庫之間有數據遷移的需求時，那麼mysql中提供的資料庫組合模式則會對數據遷移過程會有所幫助。對導出數據更容易導入目標。
在數據遷移過程中，可以設置SQL Mode 為NO_TABLE_OPTIONS 模式，這樣將去掉show create table 中的“engine”關鍵字，獲得通用的建表腳本。
-- 查看表有 engine 引擎 SHOW CREATE TABLE test_sqlmode;
-- 將所有表去掉engine 引擎。變為通用表 -- 修改 當前會話下的sql model SET SESSION sql_mode='NO_TABLE_OPTIONS'
