MySQL培训材料1

1、常用MySQL知识点一、知识点目录 (1)MySQL体系结构(2)常用表引擎(3)数据类型(4)sql语句(和Oracle有区别的)(5)常用字符、数值、日期函数等(6)字符集问题(7)SQL_MODE(8)并发控制、事务，MVCC，锁定机制(9)用户、权限、安全(10)索引(11)SQL执行计划及优化(12)查询缓存(13)MySQL高可用集群：主从replication，NDB cluster(14)预编译、全文索引、merge tables和分区支持。二、细节知识点1、mysql体系结构MySQL体系结构大概分为三层：顶层：处理connection请求，安全认证等第二层：这一层是MySQ

2、L的核心，包括代码解析，优化，缓存，以及内置函数，过程视图、触发器都在这一层工作。第三层：存储引擎层，这一层用来存储和获取数据，有点像linux的文件系统，存储引擎通过API仅和MySQL服务器进行通信？，存储引擎并不解析SQL语句，也不和其他进行通信。每种存储引擎都有自己的优点和缺点。2、常用表引擎MySQL AB引入了新的插件式存储引擎体系结构，允许将存储引擎加载到正在运新的MySQL服务器中。Mysql支持引擎：MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中Inn

3、oDB、BDB支持事务安全，其他都是非事务表（ACID），其中MyISAM是MySQL的默认存储引擎，即不指定其他引擎时默认就是MyISAM引擎，当然也可以通过storage engine更改默认引擎mysql> show variables like 'stor%'查看当前数据库引擎：mysql> show engines G在创建表或alter table的时候可以通过指定engine参数指定存储引擎。MyISAM：默认的MySQL插件式存储引擎，因为采用表级锁，所以适合以读操作和插入操作为主，而只有很少的删除和更新操作的环境，不支持事务，即无法回滚，它是在We

4、b、数据仓储和其他应用环境下最常使用的存储引擎之一。InnoDB：用于事务处理应用程序，包括ACID事务支持，支持外键，支持MVCC。采用行级锁，可以大大降低而删除和更新导致的锁定，可以支持commit和rollback，对于计费、金融对数据准确定要求较高的系统。BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性Memory：将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问，对表的大小有限制，通常用于更新不频繁的小表。Merge：允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为1个对

5、象引用它们，这样可以突破单表MyISAM的大小限制，对于诸如数据仓储等VLDB环境十分适合Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。支持MVCC。3、数据类型3.1 数值型 整数类型存储字节属性最小、最大值tinyint1Auto_incrementunsignedzerofill有符号: -1无符号: 0-1Smallint2Auto_incrementUnsignedzerofill有符号: -1无符号: 0-1Mediumint3Auto_incrementUnsignedzerofill

6、有符号: -1无符号: 0-1Int、integer4Auto_incrementUnsignedzerofill有符号: -1无符号: 0-1Bigint8Auto_incrementUnsignedzerofill有符号: -1无符号: 0-1 这些数据类型也可以指定宽度，例如int(4)，意味着如果插入数值宽度小于指定的4，则自动从左侧用空格填满宽度，并不限制保存的值的最大和最小值。当指定zerofill属性时，则默认使用unsigned即无符号，此时填充有空格改为0。属性auto_increment，用于产生唯一标识符或顺序值，仅用于整数类型，一个表中只能有一个auto_increme

7、nt的列，对于任何想使用auto_increment的列，应该定义为not null，并并定义为primary key或unique。（以上最大值，当插入数据时，MySQL工作在非严格模式下，对于无意义的则转换为允许的值，如int unsigned，插入负值，则会转化为0,超过4292967295，则转化为4292967295插入，如果工作在严格模式下，则无法插入。）浮点数类型存储字节属性最小、最大值float4Unsigned1.1754494351E-383.402823466E+38double8Unsigned2.2250738585072014E-3081.7976931348623

8、157E+38定点数类型存储字节属性最小、最大值Decimal(M,D) NUMERIC(M,D)M+2Unsigned取值范围由M和D来定位类型存储字节属性最小、最大值BIT(M)18Bit(1) bit(64)浮点数、定点数都用(M,D)，M表示共M位数字（整数+小数），D是小数点后面的长度。浮点数不指定精度，会按照硬件和操作系统默认精度。定点数不指定精度时，默认整数位10，小数位是0.两者不同的是定点数是采用字符串形式存储，存储更精确，某个列指定了浮点数或定点数的精度后，插入的精度超过实际精度，在普通模式下浮点数不会警告，而定点数会警告，都是四舍五入插入。3.2日期和时间类型日期和时间类

9、型存储字节零值表示最小、最大值DATE40000-00-001000-01-019999-12-31DATETIME80000-00-00 00:00:001000-01-01 00:00:009999-12-31 23:59:59TIMESTAMP4000000000000002038年某个时刻TIME300:00:00-838:59:59838:59:59YEAR1000019012155TIMESTAMP:也表示年月日时分秒，在列值后面+0，可获得数字值。固定宽度为19个字符，支持时间范围较小，相对于DATETIME。表中第一个TIMESTAMP列会自动设置为系统时间，timestamp

10、受MySQL版本和SQLMODE影响较大，具体参考MySQL文档。3.3字符串类型 3.3.1 CHAR(m)和VARCHAR(m) Char(m)：定长存储，m范围0-255.char列长度固定为创建表时声明的长度，当保存char值时，不足部分在尾部以空格填充保存。检索时会自动删除尾部的空格，无论检索或者存储均不进行大小写转换。 Varchar(m)：变长存储，m范围0-65535.。 两者都能存储m个字符（包括数字、字母、汉字、字符等） （注：关于NULL和,尽量不要定义NULL字段，否则会出现问题，要么在程序中控制，对于空值统一插入一个有意义的值。）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是固定长度，所以处理速度比varchar更快，缺点是浪费存储空间。不同的存储引擎对两者有不同的使用原则：MyISAM引擎：建议使用固定长度的数据列。MEMORY引擎：无论定义char或varchar都按char进行处理InnoDB：建议使用varchar列。3.3.2 BINARY(m)和VARBINARY(m) 类似于char和varchar，不同的是，它们存储的是二进制字符而不包含非二进制字符。3.3.3 BLOB和TEXTBLOB：字节字符串TINYBLOB0255字节BL

14、OB065535字节MEDIUMBLOB0167772150字节LONGBLOB04294967295字节TEXT：字符字符串TINYTEXT0255字节TEXT065535字节MEDIUMTEXT0167772150字节LONGTEXT04294967295字节二者区别：BLOB可以保存二进制数据：如照片，TEXT只能保存字符数据：比如文章。对于BLOB和TEXT使用过程中，会引起一些性能问题，特别是执行了大量了删除操作。需要定期optimize table进行碎片整理。在BLOB和TEXT数据列建索引，必须使用前缀索引，某些情况下，需要单独把这些列分离到单独的表中，减少主表的碎片。 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的运行与INSERT很相像。只有一点除外，如果表中

16、的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值，则在新记录被插入之前，旧记录被删除. REPLACE语句会返回一个数，来指示受影响的行的数目。该数是被删除和被插入的行数的和, 受影响的行数可以容易地确定是否REPLACE只添加了一行，或者是否REPLACE也替换了其它行：检查该数是否为1（添加）或更大（替换）。为了能够使用REPLACE，您必须同时拥有表的INSERT和DELETE权限。 限制查询行数: limit start_row,row_count Start_row表示开始行数、row_count表示显示的行数，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()正则表达式 Like 和Oracle类似: _表示单字符 %代表任意0-n个字符 Regexp正则表达式匹配，代表包含的意思。 匹配字符串的开始处 $ 匹配字符串的结束处 . 匹配任意单字符，包括回车换行符 * 匹配0-n个前面的字符 匹配括号内的任意字符 -代表一个范围 0-9代表任意数字 匹配不在

18、括号内的任意字符 a* 匹配0或多个a a+ 匹配1个或多个a a? 匹配0个或1个a a1| a2 匹配a1或a2 am 匹配m个a am, 匹配m个或更多个a am,n 匹配m-n个a a(,n) 匹配0-n个a 匹配特殊字符时使用 屏蔽 select 'a+2' regexp 'a+2' 1truncate table tab_name属于对ORACLE的SQL扩展，会重置AUTO_INCREMENT计数器。5、常用字符、数值、日期函数5.1字符串函数函数功能Concat(S1,S2,Sn)连接S1、S2为一个字符串INSERT(str,x,y,instr

19、)把字符串str从x位置到y个字符长的子串替换为字符串instrLOWER(str) UPPER(str)把字符串str变为大小写LEFT(str,x) RIGHT(str,x)返回字符串str最左边或最右边的x个字符串LTRIM(str) RTRIM(str) TRIM(str)删除字符串str左边或右边或两边的空格CHAR_LENGTH(str)返回值为字符串str 的长度，多字节字符算单字符，例如汉字算一个字符长度。LENGTH(str)返回值为字符串str 的长度，多字节字符算多字符，一个汉字算2个字符长度mysql> select concat('aa ',&#

20、39;bb');+-+| concat('aa ','bb') |+-+| aa bb | +-+1 row in set (0.00 sec)在SQL_MODE为pipes_as_concat，|也可以用于两个字符串连接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数值函数函数功能CEIL(x)返回大于x的最小整数值FLOOR(x)返回小于x的最大整数值RAND()返回0-1随机值ROUND(x) ROUND(x,y)四舍五入，y>0,则在小数点后y位四舍五入，y<0，在小数点左侧y位四舍五入。Truncate(x,y)截断数字x，y=0直接返回整数部分，y<0截断小数点左侧y位,y>0截断小

22、数点右侧y位5.3 日期和时间函数函数功能CURDATE() +0返回当期日期 +0格式变为YYYYMMDDCURTIME() +0返回当前时间 +0格式变为HHMMSSNOW()返回当前日期和时间DATEDIFF(expr,expr2)返回起始时间 expr和结束时间expr2之间的天数, Expr和expr2 为日期或 date-and-time 表达式DATE_ADD(date,INTERVAL expr type)返回一个日期或时间加上一个时间间隔的时间值DATE_FORMAT(date,format)根据format格式化日期date值DAYOFMONTH(date) 或 DAY(d

23、ate)返回date 对应的该月日期，范围是从 1到31DAYNAME(date)返回date对应工作日，范围周一到周日DAYOFWEEK(date) DAYOFYEAR(date)返回一周、一年的第n天Type和expr格式type 值预期的 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月份，数字形式(0.12)%D带有英语后缀的该月日期  (0th, 1st, 2nd, 3rd, .)%d该月日期, 数字形式 (00.31)%e该月日期, 数字形式(0.31)%f微秒 (000000.999999)%H小时(00.23)%h小时(01.12)%I小时 (01.12)%i分钟,数字

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

27、其中周一为每周的第一天 ; 和 %x同时使用%W工作日名称 (周日.周六)%w一周中的每日 (0=周日.6=周六)%X该周的年份，其中周日为每周的第一天, 数字形式,4位数;和%V同时使用%x该周的年份，其中周一为每周的第一天, 数字形式,4位数;和%v同时使用%Y年份, 数字形式,4位数%y年份, 数字形式 (2位数)%文字字符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流程函数(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其它函数 MD5(str) 返回字符串str的MD5值 PASSWORD(str) 返回字符串的加密版本PASSWORD()函数在MySQL服务器中的鉴定系统使用；你不应将它用在你个人的应用程序中。为达到同样目的，可使用

31、 MD5() INET_ATON(IP) 返回IP地址的数字表示 INET_NTOA(num) 返回数字代表的IP地址6、字符集字符集包括字符集和校对规则，前者确定存储方式，后者用来比较。MySQL支持多种字符集，常见Latin1、UTF8、gbk等，任何一个字符集至少有一个校对规则。校对规则一般有以下特征：（1） 两个不同的字符集不能有相同的校对规则（2） 每个字符集有一个默认校对规则（3） 它们以其相关的字符集名开始，通常包括一个语言名，并且以_ci（大小写不敏感）、_cs（大小写敏感）或_bin（二元）结束。字符集级别：（1） 服务器字符集和校对：可以在启动时通过-default-cha

32、racter-set和-default-collation，或者在编译安装时制定服务器字符集（2） 数据库字符集和校对：创建数据库时指定CHARACTER SET X和COLLATE Y，那么采用字符集X和校对规则Y。如果指定字符集没有指定校对规则，则使用字符集的默认校对规则，如果两者都不指定则使用服务器字符集和校对规则。（3） 表字符集和校对（4） 列字符集和校对（5） 连接字符集和校对，在客户端和服务器的连接处理中也涉及了字符集和校对规则变量。每一个客户端有一个连接相关的字符集和校对规则变量，涉及服务器三个变量 character_set_client 客户端发送查询使用的字符集 char

33、acter_set_connection和collation_connection 服务器端转换客户端的字符集和校对规则，collation_connection对比较文字字符串是重要的。对于列值的字符串比较，它不重要，因为列具有更高的 校对规则优先级。 character_set_results服务器返回查询结果到客户端使用的字符集 一般使用下面两个语句设置字符集：SET NAMES 'charset_name' 等价于SET character_set_client = x; SET character_set_results = x; SET character_set_

34、connection = x;SET CHARACTER SET charset_name等价于SET character_set_client = x;SET character_set_results = x; SET collation_connection = collation_database;（使用数据库默认字符集和校对规则）（6） SQL语句中使用collate，可以覆盖默认校对规则 7、SQL MODE相关 在MySQL中，通过设置不同的SQL Mode，可以完成不同程度的数据校验，从而保障数据的准确性。如果在不同的数据库之间做迁移，可以通过设置SQL Mode使MySQL更

35、方便迁移到目标数据库中。 在MySQL5.1中，SQL Mode 默认为空。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)在此种情况下，允许插入字段长度的值，插入后，MySQL会返回一个warning。可以通过修改SQL Mode为strict_trans_tables或strict_all_tables实现数据的严格校验，使错误数据不能插入表中，从而保证数据的准确性。SQL MODE设置方式：(1)启动时指定sql-mode="modes"(2)服务器运行时，通过SET SESSION|GLOBAL sql_mode='modes'设置全局或会话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严格模式事务表非事务表STRICT_TRANS_TABLES如果数据不满足表列类型，语句中断并回滚。单值插入，会进行严格校验，不允许插入。批量插入，不符合的值在第一个位置，语句中断。不符合的值不在第一个位置，后面的数据符合的正常插入，不符合转换为接近该列的合法值插入。STRICT_ALL_TABLES如果

39、数据不满足表列类型，语句中断并回滚。单值插入，会进行严格校验，不允许插入。批量插入，不符合的值在第一个位置，语句中断。不符合的值不在第一个位置，则不符合的值之前的值可以正常插入，而本身及其后面的值无法插入。一般都是组合使用：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、事务、锁、MVCCMySQL锁概述：MySQL的锁机制比较简单，不同的引擎支持不同的锁机制：MyISAM和MEMORY采用表级锁，BDB采用页级锁、Innodb支持行级锁，同时也支持表级锁。表级锁：开销小，加锁快；不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低。行级锁：开销大，加锁慢；会出现死锁，锁定粒度小，发生锁冲突的概率最低，并发度最低。页面锁：开销和加锁时间介于两者之间，会出现死锁，锁定粒度介于两者之间，并发度一般。8.1表级锁：表共享读锁（Table Read Lock）和表独占写锁（Tablet Write Lock）是否阻塞Read lockwrite lockRead lock否是write lock是是MyISAM在select之前，