MySQL培訓(xùn)材料1

1、常用MySQL知識(shí)點(diǎn)一、知識(shí)點(diǎn)目錄 (1)MySQL體系結(jié)構(gòu)(2)常用表引擎(3)數(shù)據(jù)類型(4)sql語句(和Oracle有區(qū)別的)(5)常用字符、數(shù)值、日期函數(shù)等(6)字符集問題(7)SQL_MODE(8)并發(fā)控制、事務(wù)，MVCC，鎖定機(jī)制(9)用戶、權(quán)限、安全(10)索引(11)SQL執(zhí)行計(jì)劃及優(yōu)化(12)查詢緩存(13)MySQL高可用集群：主從replication，NDB cluster(14)預(yù)編譯、全文索引、merge tables和分區(qū)支持。二、細(xì)節(jié)知識(shí)點(diǎn)1、mysql體系結(jié)構(gòu)MySQL體系結(jié)構(gòu)大概分為三層：頂層：處理connection請(qǐng)求，安全認(rèn)證等第二層：這一層是MySQ

2、L的核心，包括代碼解析，優(yōu)化，緩存，以及內(nèi)置函數(shù)，過程視圖、觸發(fā)器都在這一層工作。第三層：存儲(chǔ)引擎層，這一層用來存儲(chǔ)和獲取數(shù)據(jù)，有點(diǎn)像linux的文件系統(tǒng)，存儲(chǔ)引擎通過API僅和MySQL服務(wù)器進(jìn)行通信？，存儲(chǔ)引擎并不解析SQL語句，也不和其他進(jìn)行通信。每種存儲(chǔ)引擎都有自己的優(yōu)點(diǎn)和缺點(diǎn)。2、常用表引擎MySQL AB引入了新的插件式存儲(chǔ)引擎體系結(jié)構(gòu)，允許將存儲(chǔ)引擎加載到正在運(yùn)新的MySQL服務(wù)器中。Mysql支持引擎：MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中Inn

3、oDB、BDB支持事務(wù)安全，其他都是非事務(wù)表（ACID），其中MyISAM是MySQL的默認(rèn)存儲(chǔ)引擎，即不指定其他引擎時(shí)默認(rèn)就是MyISAM引擎，當(dāng)然也可以通過storage engine更改默認(rèn)引擎mysql> show variables like 'stor%'查看當(dāng)前數(shù)據(jù)庫(kù)引擎：mysql> show engines G在創(chuàng)建表或alter table的時(shí)候可以通過指定engine參數(shù)指定存儲(chǔ)引擎。MyISAM：默認(rèn)的MySQL插件式存儲(chǔ)引擎，因?yàn)椴捎帽砑?jí)鎖，所以適合以讀操作和插入操作為主，而只有很少的刪除和更新操作的環(huán)境，不支持事務(wù)，即無法回滾，它是在We

4、b、數(shù)據(jù)倉(cāng)儲(chǔ)和其他應(yīng)用環(huán)境下最常使用的存儲(chǔ)引擎之一。InnoDB：用于事務(wù)處理應(yīng)用程序，包括ACID事務(wù)支持，支持外鍵，支持MVCC。采用行級(jí)鎖，可以大大降低而刪除和更新導(dǎo)致的鎖定，可以支持commit和rollback，對(duì)于計(jì)費(fèi)、金融對(duì)數(shù)據(jù)準(zhǔn)確定要求較高的系統(tǒng)。BDB：可替代InnoDB的事務(wù)引擎，支持COMMIT、ROLLBACK和其他事務(wù)特性Memory：將所有數(shù)據(jù)保存在RAM中，在需要快速查找引用和其他類似數(shù)據(jù)的環(huán)境下，可提供極快的訪問，對(duì)表的大小有限制，通常用于更新不頻繁的小表。Merge：允許MySQL DBA或開發(fā)人員將一系列等同的MyISAM表以邏輯方式組合在一起，并作為1個(gè)對(duì)

5、象引用它們，這樣可以突破單表MyISAM的大小限制，對(duì)于諸如數(shù)據(jù)倉(cāng)儲(chǔ)等VLDB環(huán)境十分適合Cluster/NDB：MySQL的簇式數(shù)據(jù)庫(kù)引擎，尤其適合于具有高性能查找要求的應(yīng)用程序，這類查找需求還要求具有最高的正常工作時(shí)間和可用性。支持MVCC。3、數(shù)據(jù)類型3.1 數(shù)值型 整數(shù)類型存儲(chǔ)字節(jié)屬性最小、最大值tinyint1Auto_incrementunsignedzerofill有符號(hào): -1無符號(hào): 0-1Smallint2Auto_incrementUnsignedzerofill有符號(hào): -1無符號(hào): 0-1Mediumint3Auto_incrementUnsignedzerofill

6、有符號(hào): -1無符號(hào): 0-1Int、integer4Auto_incrementUnsignedzerofill有符號(hào): -1無符號(hào): 0-1Bigint8Auto_incrementUnsignedzerofill有符號(hào): -1無符號(hào): 0-1 這些數(shù)據(jù)類型也可以指定寬度，例如int(4)，意味著如果插入數(shù)值寬度小于指定的4，則自動(dòng)從左側(cè)用空格填滿寬度，并不限制保存的值的最大和最小值。當(dāng)指定zerofill屬性時(shí)，則默認(rèn)使用unsigned即無符號(hào)，此時(shí)填充有空格改為0。屬性auto_increment，用于產(chǎn)生唯一標(biāo)識(shí)符或順序值，僅用于整數(shù)類型，一個(gè)表中只能有一個(gè)auto_increme

7、nt的列，對(duì)于任何想使用auto_increment的列，應(yīng)該定義為not null，并并定義為primary key或unique。（以上最大值，當(dāng)插入數(shù)據(jù)時(shí)，MySQL工作在非嚴(yán)格模式下，對(duì)于無意義的則轉(zhuǎn)換為允許的值，如int unsigned，插入負(fù)值，則會(huì)轉(zhuǎn)化為0,超過4292967295，則轉(zhuǎn)化為4292967295插入，如果工作在嚴(yán)格模式下，則無法插入。）浮點(diǎn)數(shù)類型存儲(chǔ)字節(jié)屬性最小、最大值float4Unsigned1.1754494351E-383.402823466E+38double8Unsigned2.2250738585072014E-3081.7976931348623

8、157E+38定點(diǎn)數(shù)類型存儲(chǔ)字節(jié)屬性最小、最大值Decimal(M,D) NUMERIC(M,D)M+2Unsigned取值范圍由M和D來定位類型存儲(chǔ)字節(jié)屬性最小、最大值BIT(M)18Bit(1) bit(64)浮點(diǎn)數(shù)、定點(diǎn)數(shù)都用(M,D)，M表示共M位數(shù)字（整數(shù)+小數(shù)），D是小數(shù)點(diǎn)后面的長(zhǎng)度。浮點(diǎn)數(shù)不指定精度，會(huì)按照硬件和操作系統(tǒng)默認(rèn)精度。定點(diǎn)數(shù)不指定精度時(shí)，默認(rèn)整數(shù)位10，小數(shù)位是0.兩者不同的是定點(diǎn)數(shù)是采用字符串形式存儲(chǔ)，存儲(chǔ)更精確，某個(gè)列指定了浮點(diǎn)數(shù)或定點(diǎn)數(shù)的精度后，插入的精度超過實(shí)際精度，在普通模式下浮點(diǎn)數(shù)不會(huì)警告，而定點(diǎn)數(shù)會(huì)警告，都是四舍五入插入。3.2日期和時(shí)間類型日期和時(shí)間類

9、型存儲(chǔ)字節(jié)零值表示最小、最大值DATE40000-00-001000-01-019999-12-31DATETIME80000-00-00 00:00:001000-01-01 00:00:009999-12-31 23:59:59TIMESTAMP4000000000000002038年某個(gè)時(shí)刻TIME300:00:00-838:59:59838:59:59YEAR1000019012155TIMESTAMP:也表示年月日時(shí)分秒，在列值后面+0，可獲得數(shù)字值。固定寬度為19個(gè)字符，支持時(shí)間范圍較小，相對(duì)于DATETIME。表中第一個(gè)TIMESTAMP列會(huì)自動(dòng)設(shè)置為系統(tǒng)時(shí)間，timestamp

10、受MySQL版本和SQLMODE影響較大，具體參考MySQL文檔。3.3字符串類型 3.3.1 CHAR(m)和VARCHAR(m) Char(m)：定長(zhǎng)存儲(chǔ)，m范圍0-255.char列長(zhǎng)度固定為創(chuàng)建表時(shí)聲明的長(zhǎng)度，當(dāng)保存char值時(shí)，不足部分在尾部以空格填充保存。檢索時(shí)會(huì)自動(dòng)刪除尾部的空格，無論檢索或者存儲(chǔ)均不進(jìn)行大小寫轉(zhuǎn)換。 Varchar(m)：變長(zhǎng)存儲(chǔ)，m范圍0-65535.。 兩者都能存儲(chǔ)m個(gè)字符（包括數(shù)字、字母、漢字、字符等） （注：關(guān)于NULL和,盡量不要定義NULL字段，否則會(huì)出現(xiàn)問題，要么在程序中控制，對(duì)于空值統(tǒng)一插入一個(gè)有意義的值。）mysql> desc v2;+

11、-+-+-+-+-+-+| Field | Type | Null | Key | Default | Extra |+-+-+-+-+-+-+| v1 | char(4) | YES | | NULL | | v2 | varchar(4) | YES | | NULL | |+-+-+-+-+-+-+2 rows in set (0.00 sec)mysql> insert into v2(v1,v2) values('aaa',NULL),('bbb','');Query OK, 2 rows affected (0.00 sec)R

12、ecords: 2 Duplicates: 0 Warnings: 0mysql> insert into v2(v1) values('ccc');Query OK, 1 row affected (0.00 sec)mysql> select * from v2;+-+-+| v1 | v2 |+-+-+| aaa | NULL | bbb | | ccc | NULL |+-+-+3 rows in set (0.00 sec)mysql> select * from v2 where v2 is null;+-+-+| v1 | v2 |+-+-+|

13、aaa | NULL | ccc | NULL |+-+-+2 rows in set (0.00 sec)CHAR是固定長(zhǎng)度，所以處理速度比varchar更快，缺點(diǎn)是浪費(fèi)存儲(chǔ)空間。不同的存儲(chǔ)引擎對(duì)兩者有不同的使用原則：MyISAM引擎：建議使用固定長(zhǎng)度的數(shù)據(jù)列。MEMORY引擎：無論定義char或varchar都按char進(jìn)行處理InnoDB：建議使用varchar列。3.3.2 BINARY(m)和VARBINARY(m) 類似于char和varchar，不同的是，它們存儲(chǔ)的是二進(jìn)制字符而不包含非二進(jìn)制字符。3.3.3 BLOB和TEXTBLOB：字節(jié)字符串TINYBLOB0255字節(jié)BL

14、OB065535字節(jié)MEDIUMBLOB0167772150字節(jié)LONGBLOB04294967295字節(jié)TEXT：字符字符串TINYTEXT0255字節(jié)TEXT065535字節(jié)MEDIUMTEXT0167772150字節(jié)LONGTEXT04294967295字節(jié)二者區(qū)別：BLOB可以保存二進(jìn)制數(shù)據(jù)：如照片，TEXT只能保存字符數(shù)據(jù)：比如文章。對(duì)于BLOB和TEXT使用過程中，會(huì)引起一些性能問題，特別是執(zhí)行了大量了刪除操作。需要定期optimize table進(jìn)行碎片整理。在BLOB和TEXT數(shù)據(jù)列建索引，必須使用前綴索引，某些情況下，需要單獨(dú)把這些列分離到單獨(dú)的表中，減少主表的碎片。 ENU

15、M類型、SET類型略？4、SQL語句 插入多值 ：insert into tab (filed1,filed2,filed3) values (record1_value1,record1_value2,record1_value3), (record2_value1,record2_value2,record2_value3), . (record3_valuen,recordn_value2,recordn_value3)REPLACE into tab_name (col_name1,.) values (filed1,.)REPLACE的運(yùn)行與INSERT很相像。只有一點(diǎn)除外，如果表中

16、的一個(gè)舊記錄與一個(gè)用于PRIMARY KEY或一個(gè)UNIQUE索引的新記錄具有相同的值，則在新記錄被插入之前，舊記錄被刪除. REPLACE語句會(huì)返回一個(gè)數(shù)，來指示受影響的行的數(shù)目。該數(shù)是被刪除和被插入的行數(shù)的和, 受影響的行數(shù)可以容易地確定是否REPLACE只添加了一行，或者是否REPLACE也替換了其它行：檢查該數(shù)是否為1（添加）或更大（替換）。為了能夠使用REPLACE，您必須同時(shí)擁有表的INSERT和DELETE權(quán)限。 限制查詢行數(shù): limit start_row,row_count Start_row表示開始行數(shù)、row_count表示顯示的行數(shù)，limit 2,3表示顯示第3行到

17、第5；Start_row可以為空，linit n表示顯示前N行記錄。 表連接：左右連接. Right /left join Select * from t1 left/right join t2 on t1.id=t2.id 匹配：like/not like regexp/not regexp()正則表達(dá)式 Like 和Oracle類似: _表示單字符 %代表任意0-n個(gè)字符 Regexp正則表達(dá)式匹配，代表包含的意思。 匹配字符串的開始處 $ 匹配字符串的結(jié)束處 . 匹配任意單字符，包括回車換行符 * 匹配0-n個(gè)前面的字符 匹配括號(hào)內(nèi)的任意字符 -代表一個(gè)范圍 0-9代表任意數(shù)字 匹配不在

18、括號(hào)內(nèi)的任意字符 a* 匹配0或多個(gè)a a+ 匹配1個(gè)或多個(gè)a a? 匹配0個(gè)或1個(gè)a a1| a2 匹配a1或a2 am 匹配m個(gè)a am, 匹配m個(gè)或更多個(gè)a am,n 匹配m-n個(gè)a a(,n) 匹配0-n個(gè)a 匹配特殊字符時(shí)使用 屏蔽 select 'a+2' regexp 'a+2' 1truncate table tab_name屬于對(duì)ORACLE的SQL擴(kuò)展，會(huì)重置AUTO_INCREMENT計(jì)數(shù)器。5、常用字符、數(shù)值、日期函數(shù)5.1字符串函數(shù)函數(shù)功能Concat(S1,S2,Sn)連接S1、S2為一個(gè)字符串INSERT(str,x,y,instr

19、)把字符串str從x位置到y(tǒng)個(gè)字符長(zhǎng)的子串替換為字符串instrLOWER(str) UPPER(str)把字符串str變?yōu)榇笮慙EFT(str,x) RIGHT(str,x)返回字符串str最左邊或最右邊的x個(gè)字符串LTRIM(str) RTRIM(str) TRIM(str)刪除字符串str左邊或右邊或兩邊的空格CHAR_LENGTH(str)返回值為字符串str 的長(zhǎng)度，多字節(jié)字符算單字符，例如漢字算一個(gè)字符長(zhǎng)度。LENGTH(str)返回值為字符串str 的長(zhǎng)度，多字節(jié)字符算多字符，一個(gè)漢字算2個(gè)字符長(zhǎng)度mysql> select concat('aa ',&#

20、39;bb');+-+| concat('aa ','bb') |+-+| aa bb | +-+1 row in set (0.00 sec)在SQL_MODE為pipes_as_concat，|也可以用于兩個(gè)字符串連接mysql> set session sql_mode=pipes_as_concat;Query OK, 0 rows affected (0.00 sec)mysql> select sql_mode;+-+| sql_mode |+-+| PIPES_AS_CONCAT | +-+1 row in set (0.00

21、sec)mysql> select 'aa'| 'bb'+-+| 'aa'| 'bb' |+-+| aabb | +-+1 row in set (0.00 sec)5.2數(shù)值函數(shù)函數(shù)功能CEIL(x)返回大于x的最小整數(shù)值FLOOR(x)返回小于x的最大整數(shù)值RAND()返回0-1隨機(jī)值ROUND(x) ROUND(x,y)四舍五入，y>0,則在小數(shù)點(diǎn)后y位四舍五入，y<0，在小數(shù)點(diǎn)左側(cè)y位四舍五入。Truncate(x,y)截?cái)鄶?shù)字x，y=0直接返回整數(shù)部分，y<0截?cái)嘈?shù)點(diǎn)左側(cè)y位,y>0截?cái)嘈?/p>

22、數(shù)點(diǎn)右側(cè)y位5.3 日期和時(shí)間函數(shù)函數(shù)功能CURDATE() +0返回當(dāng)期日期 +0格式變?yōu)閅YYYMMDDCURTIME() +0返回當(dāng)前時(shí)間 +0格式變?yōu)镠HMMSSNOW()返回當(dāng)前日期和時(shí)間DATEDIFF(expr,expr2)返回起始時(shí)間 expr和結(jié)束時(shí)間expr2之間的天數(shù), Expr和expr2 為日期或 date-and-time 表達(dá)式DATE_ADD(date,INTERVAL expr type)返回一個(gè)日期或時(shí)間加上一個(gè)時(shí)間間隔的時(shí)間值DATE_FORMAT(date,format)根據(jù)format格式化日期date值DAYOFMONTH(date) 或 DAY(d

23、ate)返回date 對(duì)應(yīng)的該月日期，范圍是從 1到31DAYNAME(date)返回date對(duì)應(yīng)工作日，范圍周一到周日DAYOFWEEK(date) DAYOFYEAR(date)返回一周、一年的第n天Type和expr格式type 值預(yù)期的 expr 格式MICROSECONDMICROSECONDSSECONDSECONDSMINUTEMINUTESHOURHOURSDAYDAYSWEEKWEEKSMONTHMONTHSQUARTERQUARTERSYEARYEARSSECOND_MICROSECOND'SECONDS.MICROSECONDS'MINUTE_MICROS

24、ECOND'MINUTES.MICROSECONDS'MINUTE_SECOND'MINUTES:SECONDS'HOUR_MICROSECOND'HOURS.MICROSECONDS'HOUR_SECOND'HOURS:MINUTES:SECONDS'HOUR_MINUTE'HOURS:MINUTES'DAY_MICROSECOND'DAYS.MICROSECONDS'DAY_SECOND'DAYS HOURS:MINUTES:SECONDS'DAY_MINUTE'DAYS

25、 HOURS:MINUTES'DAY_HOUR'DAYS HOURS'YEAR_MONTH'YEARS-MONTHS'Format格式說明符說明%a工作日的縮寫名稱  (Sun.Sat)%b月份的縮寫名稱  (Jan.Dec)%c月份，數(shù)字形式(0.12)%D帶有英語后綴的該月日期  (0th, 1st, 2nd, 3rd, .)%d該月日期, 數(shù)字形式 (00.31)%e該月日期, 數(shù)字形式(0.31)%f微秒 (000000.999999)%H小時(shí)(00.23)%h小時(shí)(01.12)%I小時(shí) (01.12)%i分鐘,數(shù)字

26、形式 (00.59)%j一年中的天數(shù) (001.366)%k小時(shí) (0.23)%l小時(shí) (1.12)%M月份名稱 (January.December)%m月份, 數(shù)字形式 (00.12)%p上午（AM）或下午（ PM）%r時(shí)間 , 12小時(shí)制 (小時(shí)hh:分鐘mm:秒數(shù)ss 后加 AM或PM)%S秒 (00.59)%s秒 (00.59)%T時(shí)間 , 24小時(shí)制 (小時(shí)hh:分鐘mm:秒數(shù)ss)%U周 (00.53), 其中周日為每周的第一天%u周 (00.53), 其中周一為每周的第一天 %V周 (01.53), 其中周日為每周的第一天 ; 和 %X同時(shí)使用%v周 (01.53),

27、其中周一為每周的第一天 ; 和 %x同時(shí)使用%W工作日名稱 (周日.周六)%w一周中的每日 (0=周日.6=周六)%X該周的年份，其中周日為每周的第一天, 數(shù)字形式,4位數(shù);和%V同時(shí)使用%x該周的年份，其中周一為每周的第一天, 數(shù)字形式,4位數(shù);和%v同時(shí)使用%Y年份, 數(shù)字形式,4位數(shù)%y年份, 數(shù)字形式 (2位數(shù))%文字字符mysql> select date_format('19830112','%Y,%m,%d');+-+| date_format('19830112','%Y,%m,%d') |+-+| 1983

28、,01,12 | +-+1 row in set (0.00 sec)mysql> select dayname(now();+-+| dayname(now() |+-+| Friday | +-+1 row in set (0.00 sec)mysql> select datediff(now(),19830224);+-+| datediff(now(),19830224) |+-+| 9549 | +-+1 row in set (0.00 sec)mysql> select date_add(now(),interval -2 hour);+-+| date_add

29、(now(),interval -2 hour) |+-+| 2009-04-17 08:57:47 | +-+1 row in set (0.00 sec)mysql> select date_add(now(),interval -2 day);+-+| date_add(now(),interval -2 day) |+-+| 2009-04-15 10:57:54 | +-+1 row in set (0.00 sec)5.4流程函數(shù)(1)IF(value,t,f) 如果VALUE是真，返回t,否則返回f(2)IFNULL(value1,value2) 如果value1不為空則返

30、回value1,否則返回value2(3) CASE value WHEN compare-value THEN resultWHEN compare-value THEN result . ELSE result END CASE WHEN condition THEN resultWHEN condition THEN result . ELSE result END5.5其它函數(shù) MD5(str) 返回字符串str的MD5值 PASSWORD(str) 返回字符串的加密版本PASSWORD()函數(shù)在MySQL服務(wù)器中的鑒定系統(tǒng)使用；你不應(yīng)將它用在你個(gè)人的應(yīng)用程序中。為達(dá)到同樣目的，可使用

31、 MD5() INET_ATON(IP) 返回IP地址的數(shù)字表示 INET_NTOA(num) 返回?cái)?shù)字代表的IP地址6、字符集字符集包括字符集和校對(duì)規(guī)則，前者確定存儲(chǔ)方式，后者用來比較。MySQL支持多種字符集，常見Latin1、UTF8、gbk等，任何一個(gè)字符集至少有一個(gè)校對(duì)規(guī)則。校對(duì)規(guī)則一般有以下特征：（1） 兩個(gè)不同的字符集不能有相同的校對(duì)規(guī)則（2） 每個(gè)字符集有一個(gè)默認(rèn)校對(duì)規(guī)則（3） 它們以其相關(guān)的字符集名開始，通常包括一個(gè)語言名，并且以_ci（大小寫不敏感）、_cs（大小寫敏感）或_bin（二元）結(jié)束。字符集級(jí)別：（1） 服務(wù)器字符集和校對(duì)：可以在啟動(dòng)時(shí)通過-default-cha

32、racter-set和-default-collation，或者在編譯安裝時(shí)制定服務(wù)器字符集（2） 數(shù)據(jù)庫(kù)字符集和校對(duì)：創(chuàng)建數(shù)據(jù)庫(kù)時(shí)指定CHARACTER SET X和COLLATE Y，那么采用字符集X和校對(duì)規(guī)則Y。如果指定字符集沒有指定校對(duì)規(guī)則，則使用字符集的默認(rèn)校對(duì)規(guī)則，如果兩者都不指定則使用服務(wù)器字符集和校對(duì)規(guī)則。（3） 表字符集和校對(duì)（4） 列字符集和校對(duì)（5） 連接字符集和校對(duì)，在客戶端和服務(wù)器的連接處理中也涉及了字符集和校對(duì)規(guī)則變量。每一個(gè)客戶端有一個(gè)連接相關(guān)的字符集和校對(duì)規(guī)則變量，涉及服務(wù)器三個(gè)變量 character_set_client 客戶端發(fā)送查詢使用的字符集 char

33、acter_set_connection和collation_connection 服務(wù)器端轉(zhuǎn)換客戶端的字符集和校對(duì)規(guī)則，collation_connection對(duì)比較文字字符串是重要的。對(duì)于列值的字符串比較，它不重要，因?yàn)榱芯哂懈叩?校對(duì)規(guī)則優(yōu)先級(jí)。 character_set_results服務(wù)器返回查詢結(jié)果到客戶端使用的字符集 一般使用下面兩個(gè)語句設(shè)置字符集：SET NAMES 'charset_name' 等價(jià)于SET character_set_client = x; SET character_set_results = x; SET character_set_

34、connection = x;SET CHARACTER SET charset_name等價(jià)于SET character_set_client = x;SET character_set_results = x; SET collation_connection = collation_database;（使用數(shù)據(jù)庫(kù)默認(rèn)字符集和校對(duì)規(guī)則）（6） SQL語句中使用collate，可以覆蓋默認(rèn)校對(duì)規(guī)則 7、SQL MODE相關(guān) 在MySQL中，通過設(shè)置不同的SQL Mode，可以完成不同程度的數(shù)據(jù)校驗(yàn)，從而保障數(shù)據(jù)的準(zhǔn)確性。如果在不同的數(shù)據(jù)庫(kù)之間做遷移，可以通過設(shè)置SQL Mode使MySQL更

35、方便遷移到目標(biāo)數(shù)據(jù)庫(kù)中。 在MySQL5.1中，SQL Mode 默認(rèn)為空。Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 4931Server version: 5.1.32-log Source distributionType 'help;' or 'h' for help. Type 'c' to clear the buffer.mysql> select sql_mode;+-+| sql_mode |+-+|

36、| +-+1 row in set (0.00 sec)在此種情況下，允許插入字段長(zhǎng)度的值，插入后，MySQL會(huì)返回一個(gè)warning?？梢酝ㄟ^修改SQL Mode為strict_trans_tables或strict_all_tables實(shí)現(xiàn)數(shù)據(jù)的嚴(yán)格校驗(yàn)，使錯(cuò)誤數(shù)據(jù)不能插入表中，從而保證數(shù)據(jù)的準(zhǔn)確性。SQL MODE設(shè)置方式：(1)啟動(dòng)時(shí)指定sql-mode="modes"(2)服務(wù)器運(yùn)行時(shí)，通過SET SESSION|GLOBAL sql_mode='modes'設(shè)置全局或會(huì)話SQL MODEMySQL支持的所有SQL_MODE：(1)ALLOW_IN

37、VALID_DATES(2) ANSI_QUOTES(3) ERROR_FOR_DIVISION_BY_ZERO(4) HIGH_NOT_PRECEDENCE(5) IGNORE_SPACE(6) NO_AUTO_CREATE_USER(7) NO_AUTO_VALUE_ON_ZERO(8) NO_BACKSLASH_ESCAPES(9) NO_DIR_IN_CREATE(10) NO_ENGINE_SUBSTITUTION(11) NO_FIELD_OPTIONS(12) NO_KEY_OPTIONS(13) NO_TABLE_OPTIONS(14) NO_UNSIGNED_SUBTRACT

38、ION(15) NO_ZERO_DATE(16) NO_ZERO_IN_DATE(17) ONLY_FULL_GROUP_BY(18) PIPES_AS_CONCAT(19) REAL_AS_FLOAT(20) STRICT_TRANS_TABLES(21) STRICT_ALL_TABLES嚴(yán)格模式事務(wù)表非事務(wù)表STRICT_TRANS_TABLES如果數(shù)據(jù)不滿足表列類型，語句中斷并回滾。單值插入，會(huì)進(jìn)行嚴(yán)格校驗(yàn)，不允許插入。批量插入，不符合的值在第一個(gè)位置，語句中斷。不符合的值不在第一個(gè)位置，后面的數(shù)據(jù)符合的正常插入，不符合轉(zhuǎn)換為接近該列的合法值插入。STRICT_ALL_TABLES如果

39、數(shù)據(jù)不滿足表列類型，語句中斷并回滾。單值插入，會(huì)進(jìn)行嚴(yán)格校驗(yàn)，不允許插入。批量插入，不符合的值在第一個(gè)位置，語句中斷。不符合的值不在第一個(gè)位置，則不符合的值之前的值可以正常插入，而本身及其后面的值無法插入。一般都是組合使用：SQL_MODE值等同模式ANSIREAL_AS_FLOAT、PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACEDB2PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONSMAXDBPIPES_AS_CONCAT、ANSI

40、_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、 NO_AUTO_CREATE_USERMSSQLPIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、 NO_FIELD_OPTIONSMYSQL323NO_FIELD_OPTIONS、HIGH_NOT_PRECEDENCEMYSQL40NO_FIELD_OPTIONS、HIGH_NOT_PRECEDENCEORACLEPIPES_AS_CONCAT、ANSI_Q

41、UOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、NO_AUTO_CREATE_USERPOSTGRESQLPIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONSTRADITIONALSTRICT_TRANS_TABLES、STRICT_ALL_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_C

42、REATE_USER8、事務(wù)、鎖、MVCCMySQL鎖概述：MySQL的鎖機(jī)制比較簡(jiǎn)單，不同的引擎支持不同的鎖機(jī)制：MyISAM和MEMORY采用表級(jí)鎖，BDB采用頁(yè)級(jí)鎖、Innodb支持行級(jí)鎖，同時(shí)也支持表級(jí)鎖。表級(jí)鎖：開銷小，加鎖快；不會(huì)出現(xiàn)死鎖，鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低。行級(jí)鎖：開銷大，加鎖慢；會(huì)出現(xiàn)死鎖，鎖定粒度小，發(fā)生鎖沖突的概率最低，并發(fā)度最低。頁(yè)面鎖：開銷和加鎖時(shí)間介于兩者之間，會(huì)出現(xiàn)死鎖，鎖定粒度介于兩者之間，并發(fā)度一般。8.1表級(jí)鎖：表共享讀鎖（Table Read Lock）和表獨(dú)占寫鎖（Tablet Write Lock）是否阻塞Read lockwrite lockRead lock否是write lock是是MyISAM在select之前，