mysql学习之路四（转）

 MySQL存储引擎包括处理事务安全表的引擎和处理非事务安全表的引擎：

MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

例如，如果你在研究大量的临时数据，你也许需要使用内存存储引擎。内存存储引擎能够在内存中存储所有的表格数据。又或者，你也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力)。 这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)。MySQL默认配置了许多不同的存储引擎，可以预先设置或者在MySQL服务器中启用。你可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。

选择如何存储和检索你的数据的这种灵活性是MySQL为什么如此受欢迎的主要原因。其它数据库系统(包括大多数商业选择)仅支持一种类型的数据存储。遗憾的是，其它类型的数据库解决方案采取的“一个尺码满足一切需求”的方式意味着你要么就牺牲一些性能，要么你就用几个小时甚至几天的时间详细调整你的数据库。使用MySQL，我们仅需要修改我们使用的存储引擎就可以了。

默认存储引擎

MYSQL的默认存储引擎为：MyISAM，除非我们显示的指定存储引擎。如下例：

mysql> CREATE TABLE mytable (id int, title char(20)) ENGINE = INNODB;
Query OK, 0 rows affected (0.83 sec)

为了做出选择哪一个存储引擎的决定，我们首先需要考虑每一个存储引擎提供了哪些不同的核心功能。这种功能使我们能够把不同的存储引擎区别开来。我们一般把这些核心功能分为四类:支持的字段和数据类型、锁定类型、索引和处理。一些引擎具有能过促使你做出决定的独特的功能

下边我们详细讲述一下MYSQL各个存储引擎：

1  MyISAM
每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。

MyISAM引擎是大多数MySQL安装程序的默认引擎，起源于早期版本MySQL支持的ISAM引擎。这种引擎提供了最佳的性能和功能的组合，尽管它缺少事务处理功能(使用InnoDB或者BDB引擎)并且使用表级锁定。

但是执行一下查询发现，我在测试的时候使用的两个测试表在创建的时候没有指定引擎，但是发现这两个表的存储引擎都为InnoDB。（当然我们修改配职文件my.ini中的default-storage-engine=INNODB来修改）。

mysql> select table_name,engine from tables where table_name like 'test%';
+------------+--------+
| table_name | engine |
+------------+--------+
| test       | InnoDB |
| test1      | InnoDB |
+------------+--------+
2 rows in set (0.08 sec)

找了一下MYSQL文档，发现如下解释：

第15章：存储引擎和表类型:当MySQL被用MySQL配置向导安装在Windows平台上，InnoDB存储引擎替代MyISAM存储引擎作为替代，请参阅2.3.5.1节，“介绍”。


为了测试MyISAM引擎表级锁定，我们是用MyISAM引擎创建测试表TEST_ISAM。
测试中打开两个数据库连接，一个连接执行call p_tst_isam();另外一个执行单条mysql> insert into test_isam(id,mc) values(1,'1');结果在第一个连接还没有执行完的时候，第二个就完毕，没有发现MyISAM引擎锁表，这个问题我们暂时不再继续测试下去。测试中发现一个问题，MyISAM引擎的表的INSERT速度远远大于InnoDB引擎：

mysql> CREATE TABLE TEST_ISAM(ID INTEGER,MC VARCHAR(60)) ENGINE=MyISAM;        
Query OK, 0 rows affected (0.38 sec)                                          
                                                                              
mysql> select table_name,engine from information_schema.tables where table_name like 'test%';                                                                  
+------------+--------+                                                        
| table_name | engine |                                                        
+------------+--------+                                                        
| test       | InnoDB |                                                        
| test1      | InnoDB |                                                        
| test_isam  | MyISAM |                                                        
+------------+--------+                                                        
3 rows in set (0.00 sec)

创建存储过程p_test_isam

delimiter //
create procedure p_test_isam()
begin
declare counter int;
set counter = 1000000;
while counter >= 1 do
insert into test_isam(id,mc) values(counter,'test');
set counter = counter - 1;
end while;
end
//

delimiter ;

我们在以前的测试例子中： InnoDB引擎 INSERT 1000条数据花费34秒

mysql> call p_test();
Query OK, 1 row affected (34.48 sec)

MyISAM引擎INSERT 1000000 条数据花费时间20多秒：

mysql> call p_test_isam();
Query OK, 1 row affected (22.95 sec)

所以我们如果在使用非事物处理的表(也就是一些只有单用户使用的表)的时候可以采用MyISAM引擎来提高速度，当然了INSERT的时候可以利用MYSQL的BULK INSERT功能来出也是能大大提高性能的，这些我们将在MYSQL数据库优化一章中详细说明。BULK INSERT的语法：

INSERT INTO TEST VALUES(VAL11,VAL12),(VAL21,VAL22)……

测试完毕，翻看一下MYSQL文档，的确有下面一段话，和我们的测试结果吻合：

MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。MyISAM在所有MySQL配置里被支持，它是默认的存储引擎，除非你配置MySQL默认使用另外一个引擎。

2  MERGE引擎
MERGE引擎类型允许你把许多结构相同的表合并为一个表。然后，你可以执行查询，从多个表返回的结果就像从一个表返回的结果一样。每一个合并的表必须有同样的表定义。
MERGE存储引擎在下面这种使用场合会最为有用，如果需要把日志纪录不停的录入MySQL数据库，并且每天、每周或者每个月都创建一个单一的表，而且要制作来自多个表的合计查询，MERGE表这时会非常有效。然而，这项功能有局限性。你只能合并MyISAM表而且必须严格遵守相同的表定义的限制。虽然这看起来好像是一个大问题，但是，如果你使用另外一种表类型(例如InnoDB)，这种合并可能就不需要了。

3  MEMORY(内存)存储引擎

MEMORY(内存)存储引擎(以前称作HEAP存储引擎)在内存中存储全部数据。一旦MySQL服务器关闭，存储在内存中的任何信息都将丢失。然而，单个表的格式将保留，使你能够创建一个用于存储信息的临时表。这样，每次数据库服务器启动时，你不需要重新创新这个表就可以快速地访问信息。
　　长期使用MEMORY存储引擎一般来说不是一个好主意，因为数据很容易丢失。然而，如果你有足够的内存，使用基于MEMORY的表在大型数据集中执行复杂的查询是一种非常有效的方法，它能够很大程度的提高性能。
使用MEMORY表的最佳方法是使用一个“select”语句从你原来的基于磁盘的表中选择一个大型的数据集，然后对你需要的具体部分进一步分析那些信息。我过去曾经使用这个技术提取了一个月的网络记录数据，实际上就是从使用ARCHIVE存储引擎制作的表中提取的数据，然后对具体的URL、网站和其它重点进行查询。

4  EXAMPLE引擎

EXAMPLE引擎实际上是一个存储引擎编程的例子，能够用作MySQL系统中其它引擎的基础。EXAMPLE不支持数据插入，对于任何形式的数据库访问来说也不是一个实用的引擎。然而，EXAMPLE是一个很好的指南，指导你如何开发自己的存储引擎，因此对于程序员来说是一个有效的引擎。

5  InnoDB存储引擎

InnoDB给MySQL提供了具有提交，回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读。这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要，因为在InnoDB中行级锁定适合非常小的空间。InnoDB也支持FOREIGN KEY强制。在SQL查询中，你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来，甚至在同一个查询中也可以混合。
InnoDB是为处理巨大数据量时的最大性能设计。它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。
InnoDB存储引擎被完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB存储它的表＆索引在一个表空间中，表空间可以包含数个文件（或原始磁盘分区）。这与MyISAM表不同，比如在MyISAM表中每个表被存在分离的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上。
InnoDB默认地被包含在MySQL二进制分发中。Windows Essentials installer使InnoDB成为Windows上MySQL的默认表。
InnoDB被用来在众多需要高性能的大型数据库站点上产生。著名的Internet新闻站点Slashdot.org运行在InnoDB上。Mytrix, Inc.在InnoDB上存储超过1TB的数据，还有一些其它站点在InnoDB上处理平均每秒800次插入/更新的负荷。

给出一个事物控制的例子：

mysql> CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.30 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO CUSTOMER VALUES (10, 'Heikki');
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.03 sec)

mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO CUSTOMER VALUES (15, 'John');
Query OK, 1 row affected (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT * FROM CUSTOMER;
+------+--------+
| A    | B      |
+------+--------+
|   10 | Heikki |
+------+--------+
1 row in set (0.00 sec)

InnoDB存储引擎相关的内容非常复杂涉及到事物处理、日志、备份和恢复、锁定、多版本、性能、表和索引的结构、磁盘IO等很多方面的知识，我们将在以后使用中逐步研究。

我们以上只讲述了MYSQL的几个存储引擎，使我们能够对MYSQL的存储引擎有个基本的认识。MYSQL还提供了BDB (BerkeleyDB)存储引擎、FEDERATED存储引擎、ARCHIVE存储引擎、CSV存储引擎、BLACKHOLE存储引擎等，这里就不再详细说明了。更多详细信息参看MYSQL联机文档第15章：存储引擎和表类型。

4        MYSQL的SQL语法和常用函数

1 数据类型

MySQL支持所有标准SQL数值数据类型。这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

数据类型        描述        字节        推荐使用
SMALLINT        整数，从-32000到 +32000范围        2        存储相对比较小的整数。比如: 年纪，数量
INT        整数，从-2000000000 到 [phone] 范围        4        存储中等整数例如: 距离
BIGINT        不能用SMALLINT 或 INT描述的超大整数。        8        存储超大的整数例如: 科学/数学数据
FLOAT        单精度浮点型数据        4        存储小数数据例如:测量，温度
DOUBLE        双精度浮点型数据        8        需要双精度存储的小数数据例如:科学数据
DECIMAL        用户自定义精度的浮点型数据        变量;取决于精度与长度        以特别高的精度存储小数数据。例如:货币数额，科学数据
CHAR        固定长度的字符串        特定字符串长度(高达255字符)        存储通常包含预定义字符串的变量例如: 定期航线，国家或邮编
VARCHAR        具有最大限制的可变长度的字符串        变量; 1 + 实际字符串长度 (高达 255 字符)        存储不同长度的字符串值(高达一个特定的最大限度).例如:名字，密码，短文标签
TEXT        没有最大长度限制的可变长度的字符串        Variable; 2 +聽 actual string length        存储大型文本数据例如: 新闻故事，产品描述
BLOB        二进制字符串        变量；2 + 实际字符串长度        存储二进制数据例如:图片，附件，二进制文档
DATE        以 yyyy-mm-dd格式的日期        3        存储日期例如:生日，产品满期
TIME        以 hh:mm:ss格式的时间        3        存储时间或时间间隔例如:报警声，两时间之间的间隔，任务开始/结束时间
DATETIME        以yyyy-mm-ddhh:mm:ss格式结合日期和时间        8        存储包含日期和时间的数据例如:提醒的人，事件
TIMESTAMP        以yyyy-mm-ddhh:mm:ss格式结合日期和时间        4        记录即时时间例如：事件提醒器，“最后进入”的时间标记
YEAR        以 yyyy格式的年份        1        存储年份例如:毕业年，出生年
ENUM        一组数据，用户可从中选择其中一个        1或 2个字节        存储字符属性，只能从中选择之一例如:布尔量选择，如性别
SET        一组数据，用户可从中选择其中0，1或更多。        从1到8字节;取决于设置的大小        存储字符属性，可从中选择多个字符的联合。例如:多选项选择，比如业余爱好和兴趣。

2 字符串函数

CHARSET(str)                                         //返回字串字符集
CONCAT (string2  [,… ])         //连接字串，注意不要沿用ORACLE习惯

mysql> select concat('a','b');
+-----------------+
| concat('a','b') |
+-----------------+
| ab              |
+-----------------+
1 row in set (0.03 sec)

mysql> select 'a'||'b';
+----------+
| 'a'||'b' |
+----------+
|        0 |
+----------+
1 row in set, 2 warnings (0.00 sec)

INSTR (string ,substring )         //返回substring首次在string中出现的位置,不存在返回0
LCASE (string2 )                                 //转换成小写
LEFT (string2 ,length )                 //从string2中的左起取length个字符
LENGTH (string )                                 //string长度
LOAD_FILE (file_name )                 //从文件读取内容
LOCATE (substring , string  [,start_position ] )
//同INSTR,但可指定开始位置
LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度
为length
LTRIM (string2 )                                 //去除前端空格
REPEAT (string2 ,count )         //重复count次
REPLACE (str ,search_str ,replace_str )
//在str中用replace_str替换search_str
RPAD (string2 ,length ,pad)  //在str后用pad补充,直到长度length
RTRIM (string2 )                                    //去除后端空格
STRCMP (string1 ,string2 )   //逐字符比较两字串大小,
SUBSTRING (str , position  [,length ])
//从str的position开始,取length个字符
注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1
mysql> select substring('abcd',0,2);
+-----------------------+
| substring('abcd',0,2) |
+-----------------------+
|                       |
+-----------------------+
1 row in set (0.06 sec)

mysql> select substring('abcd',1,2);
+-----------------------+
| substring('abcd',1,2) |
+-----------------------+
| ab                    |
+-----------------------+
1 row in set (0.00 sec)

TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2)
//去除指定位置的指定字符
UCASE (string2 )                                 //转换成大写
RIGHT(string2,length)                 //取string2最后length个字符
SPACE(count)                                         //生成count个空格

3 数学函数

ABS (number2 )                                 //绝对值
BIN (decimal_number )                         //十进制转二进制
CEILING (number2 )                                 //向上取整
CONV(number2,from_base,to_base) //进制转换
FLOOR (number2 ) //向下取整
FORMAT (number,decimal_places ) //保留小数位数
HEX (DecimalNumber )                         //转十六进制
注：HEX()中可传入字符串，则返回其ASC-11码，如HEX(’DEF’)返回4142143
也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19

LEAST (number , number2  [,..]) //求最小值
MOD (numerator ,denominator )         //求余
POWER (number ,power )                         //求指数
RAND([seed])                                                 //随机数
ROUND (number  [,decimals ])         //四舍五入,decimals为小数位数]
注：返回类型并非均为整数，如：
(1)默认变为整形值
mysql> select round(1.23);
+————-+
| round(1.23) |
+————-+
|           1 |
+————-+
1 row in set (0.00 sec)
mysql> select round(1.56);
+————-+
| round(1.56) |
+————-+
|           2 |
+————-+
1 row in set (0.00 sec)
(2)可以设定小数位数，返回浮点型数据
mysql> select round(1.567,2);
+—————-+
| round(1.567,2) |
+—————-+
|           1.57 |
+—————-+
1 row in set (0.00 sec)

SIGN (number2 )                                         //返回符号,正负或0
SQRT(number2)                                         //开平方

4 日期函数

ADDTIME (date2 ,time_interval )                 //将time_interval加到date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ )  //转换时区
CURRENT_DATE (  )                                                 //当前日期
CURRENT_TIME (  )                                                 //当前时间
CURRENT_TIMESTAMP (  )                                         //当前时间戳
DATE (datetime )                                                         //返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间
DATE_FORMAT (datetime ,FormatCodes )         //使用formatcodes格式显示datetime
DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间
DATEDIFF (date1 ,date2 )                                 //两个日期差
DAY (date )                                                                 //返回日期的天
DAYNAME (date )                                                         //英文星期
DAYOFWEEK (date )                                                 //星期(1-7) ,1为星期天
DAYOFYEAR (date )                                                 //一年中的第几天
EXTRACT (interval_name  FROM date )         //从date中提取日期的指定部分
MAKEDATE (year ,day )                                         //给出年及年中的第几天,生成日
期串
MAKETIME (hour ,minute ,second )                 //生成时间串
MONTHNAME (date )                                                 //英文月份名
NOW (  )                                                                         //当前时间
SEC_TO_TIME (seconds )                                         //秒数转成时间
STR_TO_DATE (string ,format )                         //字串转成时间, format格式显示
TIMEDIFF (datetime1 ,datetime2 )                 //两个时间差
TIME_TO_SEC (time )                                                 //时间转秒数]
WEEK (date_time [,start_of_week ])         //第几周
YEAR (datetime )                                                         //年份
DAYOFMONTH(datetime)                                         //月的第几天
HOUR(datetime)                                                         //小时
LAST_DAY(date)                                                         //date的月的最后日期
MICROSECOND(datetime)                                         //微秒
MONTH(datetime)                                                         //月
MINUTE(datetime)                                                         //分

5 控制结构

1.区块定义，常用
begin
……
end;
也可以给区块起别名，如：
lable:begin
………..
end lable;
可以用leave lable;跳出区块，执行区块以后的代码
2.条件语句
if 条件 then
statement
else
statement
end if;
3.循环语句
(1).while循环
[label:] WHILE expression DO

statements

END WHILE [label] ;

(2).loop循环
[label:] LOOP

statements

END LOOP [label];
(3).repeat until循环
[label:] REPEAT

statements

UNTIL expression

END REPEAT [label] ;