玩技术,Geeker
一个原创技术文章分享网站

说说MySQL中的事务

果冻想阅读(1882)评论(5)

从一个问题开始

最近银行这个事情闹的比较厉害啊,很多储户的钱放在银行,就不翼而飞了,而银行还不管不问,说是用户的责任,打官司,用户还能输了,这就是“社会主义”。咱还是少发牢骚,多种树,莫谈国事。

说到银行存钱,就不得不说一下从银行取钱这件事情,从ATM机取钱这件简单的事情,实际上主要分为以下几个步骤:

  1. 登陆ATM机,输入密码;
  2. 连接数据库,验证密码;
  3. 验证成功,获得用户信息,比如存款余额等;
  4. 用户输入需要取款的金额,按下确认键;
  5. 从后台数据库中减掉用户账户上的对应金额;
  6. ATM吐出钱;
  7. 用户把钱拿走。

一个简单的取钱,主要分为以上几步。不知道大家有没有“天真”的想过,如果在第5步中,后台数据库中已经把钱减掉了,但是ATM还就是没有吐出钱(虽然实际也发生过,但是毕竟是低概率事件),这该怎么办?

关于这个问题,银行系统的开发人员早就想过了,那么他们是怎么来搞定这个问题的呢?这就要说到今天总结的事务这个概念了。

简单说说事务

对于上面的取钱这个事情,如果有一步出现了错误,那么就取消整个取钱的动作;简单来说,就是取钱这7步,要么都完成,要么就啥也不做。在数据库中,事务也是这个道理。

事务由一条或者多条sql语句组成,在事务中的操作,这些sql语句要么都执行,要么都不执行,这就是事务的目的。

对于事务而言,它需要满足ACID特性,下面就简要的说说事务的ACID特性。

  1. A,表示原子性;原子性指整个数据库事务是不可分割的工作单位。只有使事务中所有的数据库操作都执行成功,整个事务的执行才算成功。事务中任何一个sql语句执行失败,那么已经执行成功的sql语句也必须撤销,数据库状态应该退回到执行事务前的状态;
  2. C,表示一致性;也就是说一致性指事务将数据库从一种状态转变为另一种一致的状态,在事务开始之前和事务结束以后,数据库的完整性约束没有被破坏;
  3. I,表示隔离性;隔离性也叫做并发控制、可串行化或者锁。事务的隔离性要求每个读写事务的对象与其它事务的操作对象能相互分离,即该事务提交前对其它事务都不可见,这通常使用锁来实现;
  4. D,持久性,表示事务一旦提交了,其结果就是永久性的,也就是数据就已经写入到数据库了,如果发生了宕机等事故,数据库也能将数据恢复。

总结了一些事务的基本概念,在MySQL中,事务还是分为很多中的,下面就来看看到底有哪些事务。

有哪些事务

你能想象到吗?就这么个破事务还会分以下这么多种:

  1. 扁平事务;
  2. 带有保存点的扁平事务;
  3. 链事务;
  4. 嵌套事务;
  5. 分布式事务。

现在就来对这些事务从概念的层面上进行简单的总结一下。

  1. 扁平事务
    扁平事务是最简单的一种,也是实际开发中使用的最多的一种事务。在这种事务中,所有操作都处于同一层次,最常见的方式如下:

     BEGIN WORK
         Operation 1
         Operation 2
         Operation 3
         ...
         Operation N
     COMMIT WORK

    或者是这种:

     BEGIN WORK
         Operation 1
         Operation 2
         Operation 3
         ...
         Operation N
         (Error Occured)
     ROLLBACK WORK

    扁平事务的主要缺点是不能提交或回滚事务的某一部分,或者分几个独立的步骤去提交。比如有这样的一个例子,我从呼和浩特去深圳,为了便宜,我可能这么干:

     BEGIN WORK
         Operation1:呼和浩特---火车--->北京
         Operation2:北京---飞机--->深圳
     ROLLBACK WORK

    但是,如果Operation1,从呼和浩特到北京的火车晚点了,错过了航班,怎么办?感觉扁平事务的特性,那我就需要回滚,我再回到呼和浩特,那么这样成本是不是也太高了啊,所以就有了下面的第二种事务——带有保存点的扁平事务。

  2. 带有保存点的扁平事务
    这种事务除了支持扁平事务支持的操作外,允许在事务执行过程中回滚到同一事务中较早的一个状态,这是因为可能某些事务在执行过程中出现的错误并不会对所有的操作都无效,放弃整个事务不合乎要求,开销也太大。保存点用来通知系统应该记住事务当前的状态,以便以后发生错误时,事务能回到该状态。
  3. 链事务
    链事务,就是指回滚时,只能恢复到最近一个保存点;而带有保存点的扁平事务则可以回滚到任意正确的保存点。
  4. 嵌套事务
    看下面这个,你就能明白了,啥是嵌套事务:

     BEGIN WORK
         SubTransaction1:
                 BEGIN WORK
                     SubOperationX
                 COMMIT WORK
         SubTransaction2:
                 BEGIN WORK
                     SubOperationY
                 COMMIT WORK
         ...
         SubTransactionN:
                 BEGIN WORK
                     SubOperationN
                 COMMIT WORK
     COMMIT WORK

    这就是嵌套事务,在事务中再嵌套事务,位于根节点的事务称为顶层事务。事务的前驱称为父事务,其它事务称为子事务。事务的前驱称为父事务,事务的下一层称为子事务。

    子事务既可以提交也可以回滚,但是它的提交操作并不马上生效,除非由其父事务提交。因此就可以确定,任何子事务都在顶层事务提交后才真正的被提交了。同理,任意一个事务的回滚都会引起它的所有子事务一同回滚。

  5. 分布式事务
    分布式事务通常是指在一个分布式环境下运行的扁平事务,因此需要根据数据所在位置访问网络中的不同节点,比如:通过建设银行向招商银行转账,建设银行和招商银行肯定用的不是同一个数据库,同时二者的数据库也不在一个网络节点上,那么当用户跨行转账,就是通过分布式事务来保证数据的ACID的。

MySQL中使用事务

理论总结的再好,终归都要通过实践来进行理解。下面就来说说MySQL中是如何使用事务的。

在MySQL命令行的默认设置下,事务都是自动提交的,即执行SQL语句后就会马上执行COMMIT操作。因此要显示地开启一个事务须使用命令BEGIN或START TRANSACTION,或者执行命令SET AUTOCOMMIT=0,用来禁止使用当前会话的自动提交。

来看看我们可以使用哪些事务控制语句。

  • BEGIN或START TRANSACTION;显示地开启一个事务;
  • COMMIT;也可以使用COMMIT WORK,不过二者是等价的。COMMIT会提交事务,并使已对数据库进行的所有修改称为永久性的;
  • ROLLBACK;有可以使用ROLLBACK WORK,不过二者是等价的。回滚会结束用户的事务,并撤销正在进行的所有未提交的修改;
  • SAVEPOINT identifier;SAVEPOINT允许在事务中创建一个保存点,一个事务中可以有多个SAVEPOINT;
  • RELEASE SAVEPOINT identifier;删除一个事务的保存点,当没有指定的保存点时,执行该语句会抛出一个异常;
  • ROLLBACK TO identifier;把事务回滚到标记点;
  • SET TRANSACTION;用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

这些不用你“管”

有的时候有些SQL语句会产生一个隐式的提交操作,即执行完成这些语句后,会有一个隐式的COMMIT操作。有以下SQL语句,不用你去“管”:

  • DDL语句,ALTER DATABASE、ALTER EVENT、ALTER PROCEDURE、ALTER TABLE、ALTER VIEW、CREATE TABLE、DROP TABLE、RENAME TABLE、TRUNCATE TABLE等;
  • 修改MYSQL架构的语句,CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD;
  • 管理语句,ANALYZE TABLE、CACHE INDEX、CHECK TABLE、LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE等。

以上的这些SQL操作都是隐式的提交操作,不需要手动显式提交。

事务的隔离级别

上面也说到了SET TRANSACTION用来设置事务的隔离级别。那事务的隔离级别是什么东东?

在数据库操作中,为了有效保证并发读取数据的正确性,提出的事务隔离级别。

InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。这些隔离级别之间的区别如下:

隔离级别 脏读(Dirty Read) 不可重复读(NonRepeatable Read) 幻读(Phantom Read)
未提交读(Read uncommitted) 可能 可能 可能
已提交读(Read committed) 不可能 可能 可能
可重复读(Repeatable read) 不可能 不可能 可能
可串行化(Serializable ) 不可能 不可能 不可能
  • 脏读:一个事务读取到了另外一个事务没有提交的数据;
    比如:事务T1更新了一行记录的内容,但是并没有提交所做的修改。事务T2读取到了T1更新后的行,然后T1执行回滚操作,取消了刚才所做的修改。现在T2所读取的行就无效了;
  • 不可重复读:在同一事务中,两次读取同一数据,得到内容不同;
    比如:事务T1读取一行记录,紧接着事务T2修改了T1刚才读取的那一行记录。然后T1又再次读取这行记录,发现与刚才读取的结果不同。这就称为“不可重复”读,因为T1原来读取的那行记录已经发生了变化;
  • 幻读:同一事务中,用同样的操作读取两次,得到的记录数不相同;
    比如:事务T1读取一条指定的WHERE子句所返回的结果集。然后事务T2新插入 一行记录,这行记录恰好可以满足T1所使用的查询条件中的WHERE子句的条件。然后T1又使用相同的查询再次对表进行检索,但是此时却看到了事务T2刚才插入的新行。这个新行就称为“幻像”,因为对T1来说这一行就像突然出现的一样。

隔离级别越低,事务请求的锁越少或保持锁的时间就越短。InnoDB存储引擎默认的支持隔离级别是REPEATABLE READ;在这种默认的事务隔离级别下已经能完全保证事务的隔离性要求,即达到SQL标准的SERIALIZABLE级别隔离。

我们可以可以用SET TRANSACTION语句改变单个会话或者所有新进连接的隔离级别。它的语法如下:

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

注意:默认的行为(不带session和global)是为下一个(未开始)事务设置隔离级别。如果使用GLOBAL关键字,语句在全局对从那点开始创建的所有新连接(除了不存在的连接)设置默认事务级别。你需要SUPER权限来做这个。使用SESSION 关键字为将来在当前连接上执行的事务设置默认事务级别。 任何客户端都能自由改变会话隔离级别(甚至在事务的中间),或者为下一个事务设置隔离级别。

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

总结

这篇文章,基本上都是理论概念的堆积,实战的东西基本没有。但是,这些都不是问题,这也无法阻挡这篇文章成为一篇读者喜欢的文章,是吧。好了,这篇关于MySQL中事务的文章就到此结束,以后如果有新的东西,就接着总结。

2015年1月28日 于深圳。

MySQL集合操作

果冻想阅读(1454)评论(2)

啥是集合操作?

通常来说,将联接操作看作是表之间的水平操作,因为该操作生成的虚拟表包含两个表中的列。而我这里总结的集合操作,一般将这些操作看作是垂直操作。MySQL数据库支持两种集合操作:UNION DISTINCT和UNION ALL。

与联接操作一样,集合操作也是对两个输入进行操作,并生成一个虚拟表。在联接操作中,一般把输入表称为左输入和右输入。集合操作的两个输入必须拥有相同的列数,若数据类型不同,MySQL数据库自动将进行隐式转换。同时,结果列的名称由左输入决定。

前期准备

准备测试表table1和table2:

create table table1 
            (aid int not null auto_increment, 
            title varchar(20), 
            tag varchar(10), 
            primary key(aid)) 
            engine=innodb default charset=utf8;

create table table2 
            (bid int not null auto_increment, 
            title varchar(20), 
            tag varchar(10), 
            primary key(bid)) 
            engine=innodb default charset=utf8;

插入以下测试数据:

insert into table1(aid, title, tag) values(1, 'article1', 'MySQL');
insert into table1(aid, title, tag) values(2, 'article2', 'PHP');
insert into table1(aid, title, tag) values(3, 'article3', 'CPP');

insert into table2(bid, title, tag) values(1, 'article1', 'MySQL');
insert into table2(bid, title, tag) values(2, 'article2', 'CPP');
insert into table2(bid, title, tag) values(3, 'article3', 'C');

开始今天的旅行~~~

UNION DISTINCT

UNION DISTINCT组合两个输入,并应用DISTINCT过滤重复项,一般可以直接省略DISTINCT关键字,直接使用UNION。

UNION的语法如下:

SELECT column,... FROM table1 
UNION [ALL]
SELECT column,... FROM table2
...

在多个SELECT语句中,对应的列应该具有相同的字段属性,且第一个SELECT语句中被使用的字段名称也被用于结果的字段名称。

现在我运行以下sql语句:

(select * from table1) union (select * from table2);

将会得到以下结果:

+-----+----------+-------+
| aid | title    | tag   |
+-----+----------+-------+
|   1 | article1 | MySQL |
|   2 | article2 | PHP   |
|   3 | article3 | CPP   |
|   2 | article2 | CPP   |
|   3 | article3 | C     |
+-----+----------+-------+

我们发现,表table1和表table2中的重复数据项:

|   1 | article1 | MySQL |

只出现了一次,这就是UNION的作用效果。

MySQL数据库目前对UNION DISTINCT的实现方式如下:

  1. 创建一张临时表,也就是虚拟表;
  2. 对这张临时表的列添加唯一索引;
  3. 将输入的数据插入临时表;
  4. 返回虚拟表。

因为添加了唯一索引,所以可以过滤掉集合中重复的数据项。这里重复的意思是SELECT所选的字段完全相同时,才会算作是重复的。

UNION ALL

UNION ALL的意思是不会排除掉重复的数据项,比如我运行以下的sql语句:

(select * from table1) union all (select * from table2);

你将会得到以下结果:

+-----+----------+-------+
| aid | title    | tag   |
+-----+----------+-------+
|   1 | article1 | MySQL |
|   2 | article2 | PHP   |
|   3 | article3 | CPP   |
|   1 | article1 | MySQL |
|   2 | article2 | CPP   |
|   3 | article3 | C     |
+-----+----------+-------+

发现重复的数据并不会被筛选掉。

在使用UNION DISTINCT的时候,由于向临时表中添加了唯一索引,插入的速度显然会因此而受到影响。如果确认进行UNION操作的两个集合中没有重复的选项,最有效的办法应该是使用UNION ALL。

总结

总结完毕,交卷,走人,下班。

2015年1月26日 于深圳。

MySQL联接查询操作

果冻想阅读(929)评论(2)

前言

现在系统的各种业务是如此的复杂,数据都存在数据库中的各种表中,这个主键啊,那个外键啊,而表与表之间就依靠着这些主键和外键联系在一起。而我们进行业务操作时,就需要在多个表之间,使用sql语句建立起关系,然后再进行各种sql操作。那么在使用sql写出各种操作时,如何使用sql语句,将多个表关联在一起,进行业务操作呢?而这篇文章,就对这个知识点进行总结。

联接查询是一种常见的数据库操作,即在两张表(多张表)中进行匹配的操作。MySQL数据库支持如下的联接查询:

  • CROSS JOIN(交叉联接)
  • INNER JOIN(内联接)
  • OUTER JOIN(外联接)
  • 其它

在进行各种联接操作时,一定要回忆一下在《SQL逻辑查询语句执行顺序》这篇文章中总结的SQL逻辑查询语句执行的前三步:

  • 执行FROM语句(笛卡尔积)
  • 执行ON过滤
  • 添加外部行

每个联接都只发生在两个表之间,即使FROM子句中包含多个表也是如此。每次联接操作也只进行逻辑查询语句的前三步,每次产生一个虚拟表,这个虚拟表再依次与FROM子句的下一个表进行联接,重复上述步骤,直到FROM子句中的表都被处理完为止。

前期准备

  1. 新建一个测试数据库TestDB;
      create database TestDB;
  2. 创建测试表table1和table2;
     CREATE TABLE table1
     (
         customer_id VARCHAR(10) NOT NULL,
         city VARCHAR(10) NOT NULL,
         PRIMARY KEY(customer_id)
     )ENGINE=INNODB DEFAULT CHARSET=UTF8;
    
     CREATE TABLE table2
     (
         order_id INT NOT NULL auto_increment,
         customer_id VARCHAR(10),
         PRIMARY KEY(order_id)
     )ENGINE=INNODB DEFAULT CHARSET=UTF8;
  3. 插入测试数据;
     INSERT INTO table1(customer_id,city) VALUES('163','hangzhou');
     INSERT INTO table1(customer_id,city) VALUES('9you','shanghai');
     INSERT INTO table1(customer_id,city) VALUES('tx','hangzhou');
     INSERT INTO table1(customer_id,city) VALUES('baidu','hangzhou');
    
     INSERT INTO table2(customer_id) VALUES('163');
     INSERT INTO table2(customer_id) VALUES('163');
     INSERT INTO table2(customer_id) VALUES('9you');
     INSERT INTO table2(customer_id) VALUES('9you');
     INSERT INTO table2(customer_id) VALUES('9you');
     INSERT INTO table2(customer_id) VALUES('tx');

    准备工作做完以后,table1和table2看起来应该像下面这样:

     mysql> select * from table1;
     +-------------+----------+
     | customer_id | city     |
     +-------------+----------+
     | 163         | hangzhou |
     | 9you        | shanghai |
     | baidu       | hangzhou |
     | tx          | hangzhou |
     +-------------+----------+
     4 rows in set (0.00 sec)
    
     mysql> select * from table2;
     +----------+-------------+
     | order_id | customer_id |
     +----------+-------------+
     |        1 | 163         |
     |        2 | 163         |
     |        3 | 9you        |
     |        4 | 9you        |
     |        5 | 9you        |
     |        6 | tx          |
     +----------+-------------+
     7 rows in set (0.00 sec)

准备工作做的差不多了,开始今天的总结吧。

CROSS JOIN联接(交叉联接)

CROSS JOIN对两个表执行FROM语句(笛卡尔积)操作,返回两个表中所有列的组合。如果左表有m行数据,右表有n行数据,则执行CROSS JOIN将返回m*n行数据。CROSS JOIN只执行SQL逻辑查询语句执行的前三步中的第一步。

CROSS JOIN可以干什么?由于CROSS JOIN只执行笛卡尔积操作,并不会进行过滤,所以,我们在实际中,可以使用CROSS JOIN生成大量的测试数据。

对上述测试数据,使用以下查询:

select * from table1 cross join table2;

就会得到以下结果:

+-------------+----------+----------+-------------+
| customer_id | city     | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163         | hangzhou |        1 | 163         |
| 9you        | shanghai |        1 | 163         |
| baidu       | hangzhou |        1 | 163         |
| tx          | hangzhou |        1 | 163         |
| 163         | hangzhou |        2 | 163         |
| 9you        | shanghai |        2 | 163         |
| baidu       | hangzhou |        2 | 163         |
| tx          | hangzhou |        2 | 163         |
| 163         | hangzhou |        3 | 9you        |
| 9you        | shanghai |        3 | 9you        |
| baidu       | hangzhou |        3 | 9you        |
| tx          | hangzhou |        3 | 9you        |
| 163         | hangzhou |        4 | 9you        |
| 9you        | shanghai |        4 | 9you        |
| baidu       | hangzhou |        4 | 9you        |
| tx          | hangzhou |        4 | 9you        |
| 163         | hangzhou |        5 | 9you        |
| 9you        | shanghai |        5 | 9you        |
| baidu       | hangzhou |        5 | 9you        |
| tx          | hangzhou |        5 | 9you        |
| 163         | hangzhou |        6 | tx          |
| 9you        | shanghai |        6 | tx          |
| baidu       | hangzhou |        6 | tx          |
| tx          | hangzhou |        6 | tx          |
+-------------+----------+----------+-------------+

INNER JOIN联接(内联接)

INNER JOIN比CROSS JOIN强大的一点在于,INNER JOIN可以根据一些过滤条件来匹配表之间的数据。在SQL逻辑查询语句执行的前三步中,INNER JOIN会执行第一步和第二步;即没有第三步,不添加外部行,这是INNER JOIN和接下来要说的OUTER JOIN的最大区别之一。

现在来看看使用INNER JOIN来查询一下:

select * 
from table1 
inner join table2 
on table1.customer_id=table2.customer_id;

就会得到以下结果:

+-------------+----------+----------+-------------+
| customer_id | city     | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163         | hangzhou |        1 | 163         |
| 163         | hangzhou |        2 | 163         |
| 9you        | shanghai |        3 | 9you        |
| 9you        | shanghai |        4 | 9you        |
| 9you        | shanghai |        5 | 9you        |
| tx          | hangzhou |        6 | tx          |
+-------------+----------+----------+-------------+

对于INNER JOIN来说,如果没有使用ON条件的过滤,INNER JOIN和CROSS JOIN的效果是一样的。当在ON中设置的过滤条件列具有相同的名称,我们可以使用USING关键字来简写ON的过滤条件,这样可以简化sql语句,例如:

select * from table1 inner join table2 using(customer_id);

在实际编写sql语句时,我们都可以省略掉INNER关键字,例如:

select * 
from table1 
join table2 
on table1.customer_id=table2.customer_id;

但是,请记住,这还是INNER JOIN。

OUTER JOIN联接(外联接)

哦,记得有一次参加面试,还问我这个问题来着,那在这里再好好的总结一下。通过OUTER JOIN,我们可以按照一些过滤条件来匹配表之间的数据。OUTER JOIN的结果集等于INNER JOIN的结果集加上外部行;也就是说,在使用OUTER JOIN时,SQL逻辑查询语句执行的前三步,都会执行一遍。关于如何添加外部行,请参考《SQL逻辑查询语句执行顺序》这篇文章中的添加外部行部分内容。

MySQL数据库支持LEFT OUTER JOIN和RIGHT OUTER JOIN,与INNER关键字一样,我们可以省略OUTER关键字。对于OUTER JOIN,同样的也可以使用USING来简化ON子句。所以,对于以下sql语句:

select * 
from table1 
left outer join table2 
on table1.customer_id=table2.customer_id;

我们可以简写成这样:

select * 
from table1 
left join table2 
using(customer_id);

但是,与INNER JOIN还有一点区别是,对于OUTER JOIN,必须指定ON(或者using)子句,否则MySQL数据库会抛出异常。

NATURAL JOIN联接(自然连接)

NATURAL JOIN等同于INNER(OUTER) JOIN与USING的组合,它隐含的作用是将两个表中具有相同名称的列进行匹配。同样的,NATURAL LEFT(RIGHT) JOIN等同于LEFT(RIGHT) JOIN与USING的组合。比如:

select * 
from table1 
join table2 
using(customer_id);

select * 
from table1 
natural join table2;

等价。

在比如:

select * 
from table1 
left join table2 
using(customer_id);

select * 
from table1 
natural left join table2;

等价。

STRAIGHT_JOIN联接

STRAIGHT_JOIN并不是一个新的联接类型,而是用户对sql优化器的控制,其等同于JOIN。通过STRAIGHT_JOIN,MySQL数据库会强制先读取左边的表。举个例子来说,比如以下sql语句:

explain select * 
from table1 join table2 
on table1.customer_id=table2.customer_id;

它的主要输出部分如下:

+----+-------------+--------+------+---------------+
| id | select_type | table  | type | possible_keys |
+----+-------------+--------+------+---------------+
|  1 | SIMPLE      | table2 | ALL  | NULL          |
|  1 | SIMPLE      | table1 | ALL  | PRIMARY       |
+----+-------------+--------+------+---------------+

我们可以很清楚的看到,MySQL是先选择的table2表,然后再进行的匹配。如果我们指定STRAIGHT_JOIN方式,例如:

explain select * 
from table1 straight_join table2 
on table1.customer_id=table2.customer_id;

上述语句的主要输出部分如下:

+----+-------------+--------+------+---------------+
| id | select_type | table  | type | possible_keys |
+----+-------------+--------+------+---------------+
|  1 | SIMPLE      | table1 | ALL  | PRIMARY       |
|  1 | SIMPLE      | table2 | ALL  | NULL          |
+----+-------------+--------+------+---------------+

可以看到,当指定STRAIGHT_JOIN方式以后,MySQL就会先选择table1表,然后再进行的匹配。

那么就有读者问了,这有啥好处呢?性能,还是性能。由于我这里测试数据比较少,大进行大量数据的访问时,我们指定STRAIGHT_JOIN让MySQL先读取左边的表,让MySQL按照我们的意愿来完成联接操作。在进行性能优化时,我们可以考虑使用STRAIGHT_JOIN。

多表联接

在上面的所有例子中,我都是使用的两个表之间的联接,而更多时候,我们在工作中,可能不止要联接两张表,可能要涉及到三张或者更多张表的联接查询操作。

对于INNER JOIN的多表联接查询,可以随意安排表的顺序,而不会影响查询的结果。这是因为优化器会自动根据成本评估出访问表的顺序。如果你想指定联接顺序,可以使用上面总结的STRAIGHT_JOIN。

而对于OUTER JOIN的多表联接查询,表的位置不同,涉及到添加外部行的问题,就可能会影响最终的结果。

总结

这是MySQL中联接操作的全部内容了,内容虽多,但是都还比较简单,结合文章中的例子,再自己实际操作一遍,完全可以搞定的。这一篇文章就这样了。

2015年1月22日 于深圳。

MySQL子查询

果冻想阅读(1502)评论(4)

准备工作

首先声明下,一切测试操作都是在MySQL数据库上完成,关于MySQL数据库的一些简单操作,请阅读以下文章,自行补脑:

继续做以下的前期准备工作:

  1. 新建一个测试数据库TestDB;
      create database TestDB;
  2. 创建测试表table1和table2;
     CREATE TABLE table1
     (
         customer_id VARCHAR(10) NOT NULL,
         city VARCHAR(10) NOT NULL,
         PRIMARY KEY(customer_id)
     )ENGINE=INNODB DEFAULT CHARSET=UTF8;
    
     CREATE TABLE table2
     (
         order_id INT NOT NULL auto_increment,
         customer_id VARCHAR(10),
         PRIMARY KEY(order_id)
     )ENGINE=INNODB DEFAULT CHARSET=UTF8;
  3. 插入测试数据;
     INSERT INTO table1(customer_id,city) VALUES('163','hangzhou');
     INSERT INTO table1(customer_id,city) VALUES('9you','shanghai');
     INSERT INTO table1(customer_id,city) VALUES('tx','hangzhou');
     INSERT INTO table1(customer_id,city) VALUES('baidu','hangzhou');
    
     INSERT INTO table2(customer_id) VALUES('163');
     INSERT INTO table2(customer_id) VALUES('163');
     INSERT INTO table2(customer_id) VALUES('9you');
     INSERT INTO table2(customer_id) VALUES('9you');
     INSERT INTO table2(customer_id) VALUES('9you');
     INSERT INTO table2(customer_id) VALUES('tx');

    准备工作做完以后,table1和table2看起来应该像下面这样:

     mysql> select * from table1;
     +-------------+----------+
     | customer_id | city     |
     +-------------+----------+
     | 163         | hangzhou |
     | 9you        | shanghai |
     | baidu       | hangzhou |
     | tx          | hangzhou |
     +-------------+----------+
     4 rows in set (0.00 sec)
    
     mysql> select * from table2;
     +----------+-------------+
     | order_id | customer_id |
     +----------+-------------+
     |        1 | 163         |
     |        2 | 163         |
     |        3 | 9you        |
     |        4 | 9you        |
     |        5 | 9you        |
     |        6 | tx          |
     +----------+-------------+
     7 rows in set (0.00 sec)

准备工作做的差不多了,开始今天的总结吧。

一个问题

现在需要查询所有杭州用户的所有订单号,这个SQL语句怎么写?首先,你可以这么写:

select table2.customer_id, table2.order_id from table2 join table1 on table1.customer_id=table2.customer_id where table1.city='hangzhou';

能实现我们需要的结果。但是,我们也可以这么写:

select customer_id, order_id from table2 where customer_id in (select customer_id from table1 where city='hangzhou');

呃?在()括号中的的select语句是什么?问题来了,这到底是什么语法,怎么也可以完成任务,那么这篇博文就围绕着这个问题开始展开。

啥是子查询

简单的说,子查询就是:

子查询就是指在一个select语句中嵌套另一个select语句。同时,子查询必须包含括号。

alt

如上图所示,子查询,有叫内部查询,相对于内部查询,包含内部查询的就称为外部查询。子查询可以包含普通select可以包括的任何子句,比如:distinct、group by、order by、limit、join和union等;但是对应的外部查询必须是以下语句之一:select、insert、update、delete、set或者do。

alt

我们可以在where和having子句中使用子查询,将子查询得到的结果作为判断的条件。

使用比较进行子查询

一个子查询会返回一个标量(就一个值)、一个行、一个列或一个表,这些子查询称之为标量、行、列和表子查询。

当一个子查询返回一个标量时,我们就可以在where或者having子句中使用比较符与子查询得到的结果进行直接判断。比如,我现在要得到比用户tx订单数多的customer_id、city和订单数,这个sql语句怎么写。

先来说说,我写sql的一般步骤:

  1. 读懂需求;
    得到比用户tx订单数多的customer_id、city和对应的订单数。
  2. 看看最终需要得到哪些字段信息;
    最终需要得到customer_id、city和订单数信息。
  3. 分析这些字段信息涉及到哪几个表;
    涉及到表table1和表table2。
  4. 这几个表是如何关联的;
    表table1和表table2的关联就在于customer_id字段。
  5. 分解需求,得到一个个小的需求;
    • 需要得到tx用户的订单数;
    • 需要得到其它用户的订单数;
    • 比较订单数。
  6. 确认每一个小需求的过滤条件;
  7. 得到每个小需求的结果,进行组装,得到最终结果。

最终,我会写出一下的sql语句:

select table1.customer_id,city,count(order_id) 
from table1 join table2 
on table1.customer_id=table2.customer_id 
where table1.customer_id <> 'tx'
group by customer_id 
having count(order_id) > 
                        (select count(order_id) 
                         from table2 
                         where customer_id='tx' 
                         group by customer_id);

上面的查询中使用了子查询,外部查询与子查询得到的结果进行了比较判断。如果子查询返回一个标量值(就一个值),那么外部查询就可以使用:=、>、<、>=、<=和<>符号进行比较判断;如果子查询返回的不是一个标量值,而外部查询使用了比较符和子查询的结果进行了比较,那么就会抛出异常。

使用ANY进行子查询

上面使用比较符进行子查询,规定了子查询只能返回一个标量值;但是,如果子查询返回的是一个集合,怎么办?

没问题,我们可以使用:any、in、some或者all来和子查询的返回结果进行条件判断。这里先总结使用any进行子查询。

any关键词必须与上面总结的比较操作符一起使用;any关键词的意思是“对于子查询返回的列中的任何一个数值,如果比较结果为TRUE,就返回TRUE”。

好比“10 >any(11, 20, 2, 30)”,由于10>2,所以,该该判断会返回TRUE;只要10与集合中的任意一个进行比较,得到TRUE时,就会返回TRUE。

比如,我现在要查询比customer_id为tx或者9you的订单数量多的用户的id、城市和订单数量。

我可以得到以下的sql语句来完成需求。

select table1.customer_id,city,count(order_id)
from table1 join table2
on table1.customer_id=table2.customer_id
where table1.customer_id<>'tx' and table1.customer_id<>'9you'
group by customer_id
having count(order_id) >
any (
select count(order_id)
from table2
where customer_id='tx' or customer_id='9you'
group by customer_id);

any的意思比较好明白,直译就是任意一个,只要条件满足任意的一个,就返回TRUE。

使用IN进行子查询

使用in进行子查询,这个我们在日常写sql的时候是经常遇到的。in的意思就是指定的一个值是否在这个集合中,如何在就返回TRUE;否则就返回FALSE了。

in是“=any”的别名,在使用“=any”的地方,我们都可以使用“in”来进行替换。这里就不举例了,尽情的发挥想象,自行发挥吧。

有了in,肯定就有了not in;not in并不是和<>any是同样的意思,not in和<>all是一个意思,关于all,下面马上就要总结了。

使用SOME进行子查询

some是any的别名,用的比较少。只需要理解any的意思就好了,这里就不做过多的总结。具体请参考上面的any部分的总结。

使用ALL进行子查询

all必须与比较操作符一起使用。all的意思是“对于子查询返回的列中的所有值,如果比较结果为TRUE,则返回TRUE”。

好比“10 >all(2, 4, 5, 1)”,由于10大于集合中的所有值,所以这条判断就返回TRUE;而如果为“10 >all(20, 3, 2, 1, 4)”,这样的话,由于10小于20,所以该判断就会返回FALSE。

<>all的同义词是not in,表示不等于集合中的所有值,这个很容易和<>any搞混,平时多留点心就好了。

标量子查询

根据子查询返回值的数量,将子查询可以分为标量子查询和多值子查询。在使用比较符进行子查询时,就要求必须是标量子查询;如果是多值子查询时,使用比较符,就会抛出异常。

多值子查询

与标量子查询对应的就是多值子查询了,多值子查询会返回一列、一行或者一个表,它们组成一个集合。我们一般使用的any、in、all和some等词,将外部查询与子查询的结果进行判断。如果将any、in、all和some等词与标量子查询,就会得到空的结果。

独立子查询

独立子查询是不依赖外部查询而运行的子查询。什么叫依赖外部查询?先看下面两个sql语句。

sql语句1:获得所有hangzhou顾客的订单号。

select order_id 
from table2 
where customer_id in 
                   (select customer_id 
                   from table1 
                   where city='hangzhou');

sql语句2:获得城市为hangzhou,并且存在订单的用户。

select * 
from table1 
where city='hangzhou' and exists
                               (select * 
                               from table2 
                               where table1.customer_id=table2.customer_id);

上面的两条sql语句,虽然例子举的有点不是很恰当,但是足以说明这里的问题了。

对于sql语句1,我们将子查询单独复制出来,也是可以单独执行的,就是子查询与外部查询没有任何关系。

对于sql语句2,我们将子查询单独复制出来,就无法单独执行了,由于sql语句2的子查询依赖外部查询的某些字段,这就导致子查询就依赖外部查询,就产生了相关性。

对于子查询,很多时候都会考虑到效率的问题。当我们执行一个select语句时,可以加上explain关键字,用来查看查询类型,查询时使用的索引以及其它等等信息。比如这么用:

explain select order_id 
    from table2 
    where customer_id in 
                       (select customer_id 
                       from table1 
                       where city='hangzhou');

使用独立子查询,如果子查询部分对集合的最大遍历次数为n,外部查询的最大遍历次数为m时,我们可以记为:O(m+n)。而如果使用相关子查询,它的遍历次数可能会达到O(m+m*n)。可以看到,效率就会成倍的下降;所以,大伙在使用子查询时,一定要考虑到子查询的相关性。

关于explain的更多解释,请参考这里

相关子查询

相关子查询是指引用了外部查询列的子查询,即子查询会对外部查询的每行进行一次计算。但是在MySQL的内部,会进行动态优化,会随着情况的不同会有所不同。使用相关子查询是最容易出现性能的地方。而关于sql语句的优化,这又是一个非常大的话题了,只能通过实际的经验积累,才能更好的去理解如何进行优化。

关于sql的性能,我这里不能说什么,如果只是阅读其它人的文章来考虑性能问题,其实是没有任何感觉的,我们需要实际的项目中才能更好的理解。

EXISTS谓词

EXISTS是一个非常牛叉的谓词,它允许数据库高效地检查指定查询是否产生某些行。根据子查询是否返回行,该谓词返回TRUE或FALSE。与其它谓词和逻辑表达式不同的是,无论输入子查询是否返回行,EXISTS都不会返回UNKNOWN,对于EXISTS来说,UNKNOWN就是FALSE。还是上面的语句,获得城市为hangzhou,并且存在订单的用户。

select * 
from table1 
where city='hangzhou' and exists
                               (select * 
                               from table2 
                               where table1.customer_id=table2.customer_id);

使用explain查看一下,就会得到以下内容:

alt

我们可以很明显的看到,存在一个相关的子查询(DEPENDENT SUBQUERY)。可以看到EXISTS和IN是非常相似的,那么它们之间的区别是什么呢?

关于IN和EXISTS的主要区别在于三值逻辑的判断上。EXISTS总是返回TRUE或FALSE,而对于IN,除了TRUE、FALSE值外,还有可能对NULL值返回UNKNOWN。但是在过滤器中,UNKNOWN的处理方式与FALSE相同,因此使用IN与使用EXISTS一样,SQL优化器会选择相同的执行计划。

说到了IN和EXISTS几乎是一样的,但是,就不得不说到NOT IN和NOT EXISTS,对于输入列表中包含NULL值时,NOT EXISTS和NOT IN之间的差异就表现的非常大了。输入列表包含NULL值时,IN总是返回TRUE和UNKNOWN,因此NOT IN就会得到NOT TRUE和NOT UNKNOWN,即FALSE和UNKNOWN。

mysql> select 'c' NOT IN ('a', 'b', NULL)\G;

执行一下上述代码,看看结果。你就会感到惊讶。

派生表

上面也说到了,在子查询返回的值中,也可能返回一个表,如果将子查询返回的虚拟表再次作为FROM子句的输入时,这就子查询的虚拟表就成为了一个派生表。语法结构如下:

FROM (subquery expression) AS derived_table_alias

由于派生表是完全的虚拟表,并没有也不可能被物理地具体化。

总结

总算总结的差不多了,当然了子查询的东西还是有很多的,不可能一篇文章就能总结的完的,这里只是把一些基本的概念,常用的知识点进行了总结,关于将子查询使用到update、delete和insert语句中的用法,我这里并没有涉及,大体上都是大同小异的。知识这个东西,展开了,就没有头了,还是需要适可而止,适当的进行深度的挖掘,但是深度最好不要超过2,关于这个2如何定义,自行把握。好了,这篇文章就到此为止了,我们下一篇见。

2015年1月21日 于深圳。

SQL逻辑查询语句执行顺序

果冻想阅读(3432)评论(34)

我的抱怨

我一个搞应用开发的,非要会数据库,这不是专门的数据库开发人员干的事么?话说,小公司也没有数据库开发人员这么个职位吧。好吧,对数据库最深的印象还停留在大学《数据库原理》这堂课上,什么第一范式,第二范式…,这些理论的东西,多多少少还是记得点,至于更深层次的,我不会。所以呢,撸起袖子,开始学习吧。

干程序员,最不怕的就是学习,如果你连学习都怕了,那还是早点退出这行吧。你说是吧。而我今天这篇文章,既不总结什么深奥的理论,也不总结多么高深的架构(我也不会)。就从最基本的SELECT语句开始吧。

最后,这篇文章是我读《MySQL技术内幕:SQL编程》而总结出来的,对于书中有的东西讲的比较“粗”,可能是我的水平没有达到人家作者要求的水平,导致阅读起来,不是很舒服,所以,这篇博文,将会非常细致的进行总结。只有你想不到,没有你做不到。

能看懂么?

先来一段伪代码,首先你能看懂么?

SELECT DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>

如果你知道每个关键字的意思,作用,如果你还用过的话,那再好不过了。但是,你知道这些语句,它们的执行顺序你清楚么?如果你非常清楚,你就没有必要再浪费时间继续阅读了;如果你不清楚,非常好,你应该庆幸你阅读到了这么好的一篇文章。

准备工作

首先声明下,一切测试操作都是在MySQL数据库上完成,关于MySQL数据库的一些简单操作,请阅读一下文章:

继续做以下的前期准备工作:

  1. 新建一个测试数据库TestDB;
      create database TestDB;
  2. 创建测试表table1和table2;
     CREATE TABLE table1
     (
         customer_id VARCHAR(10) NOT NULL,
         city VARCHAR(10) NOT NULL,
         PRIMARY KEY(customer_id)
     )ENGINE=INNODB DEFAULT CHARSET=UTF8;
    
     CREATE TABLE table2
     (
         order_id INT NOT NULL auto_increment,
         customer_id VARCHAR(10),
         PRIMARY KEY(order_id)
     )ENGINE=INNODB DEFAULT CHARSET=UTF8;
  3. 插入测试数据;
     INSERT INTO table1(customer_id,city) VALUES('163','hangzhou');
     INSERT INTO table1(customer_id,city) VALUES('9you','shanghai');
     INSERT INTO table1(customer_id,city) VALUES('tx','hangzhou');
     INSERT INTO table1(customer_id,city) VALUES('baidu','hangzhou');
    
     INSERT INTO table2(customer_id) VALUES('163');
     INSERT INTO table2(customer_id) VALUES('163');
     INSERT INTO table2(customer_id) VALUES('9you');
     INSERT INTO table2(customer_id) VALUES('9you');
     INSERT INTO table2(customer_id) VALUES('9you');
     INSERT INTO table2(customer_id) VALUES('tx');
     INSERT INTO table2(customer_id) VALUES(NULL);

    准备工作做完以后,table1和table2看起来应该像下面这样:

     mysql> select * from table1;
     +-------------+----------+
     | customer_id | city     |
     +-------------+----------+
     | 163         | hangzhou |
     | 9you        | shanghai |
     | baidu       | hangzhou |
     | tx          | hangzhou |
     +-------------+----------+
     4 rows in set (0.00 sec)
    
     mysql> select * from table2;
     +----------+-------------+
     | order_id | customer_id |
     +----------+-------------+
     |        1 | 163         |
     |        2 | 163         |
     |        3 | 9you        |
     |        4 | 9you        |
     |        5 | 9you        |
     |        6 | tx          |
     |        7 | NULL        |
     +----------+-------------+
     7 rows in set (0.00 sec)
  4. 准备SQL逻辑查询测试语句
     SELECT a.customer_id, COUNT(b.order_id) as total_orders
     FROM table1 AS a
     LEFT JOIN table2 AS b
     ON a.customer_id = b.customer_id
     WHERE a.city = 'hangzhou'
     GROUP BY a.customer_id
     HAVING count(b.order_id) < 2
     ORDER BY total_orders DESC;

    使用上述SQL查询语句来获得来自杭州,并且订单数少于2的客户。

好吧,这些测试表和测试数据均来自《MySQL技术内幕:SQL编程》,这应该不算抄袭吧,借鉴借鉴啊。

万事俱备,只欠东风。接下来开始这篇文章最正式的部分吧。

SQL逻辑查询语句执行顺序

还记得上面给出的那一长串的SQL逻辑查询规则么?那么,到底哪个先执行,哪个后执行呢?现在,我先给出一个查询语句的执行顺序:

(7)     SELECT 
(8)     DISTINCT <select_list>
(1)     FROM <left_table>
(3)     <join_type> JOIN <right_table>
(2)     ON <join_condition>
(4)     WHERE <where_condition>
(5)     GROUP BY <group_by_list>
(6)     HAVING <having_condition>
(9)     ORDER BY <order_by_condition>
(10)    LIMIT <limit_number>

上面在每条语句的前面都标明了执行顺序号,不要问我怎么知道这个顺序的。我也是读各种“武林秘籍”才得知的,如果你有功夫,去阅读一下MySQL的源码,也会得出这个结果的。

好了,上面我标出了各条查询规则的执行先后顺序,那么各条查询语句是如何执行的呢?这就是我今天这篇博文的重点内容。Go on…

执行FROM语句

在这些SQL语句的执行过程中,都会产生一个虚拟表,用来保存SQL语句的执行结果(这是重点),我现在就来跟踪这个虚拟表的变化,得到最终的查询结果的过程,来分析整个SQL逻辑查询的执行顺序和过程。

第一步,执行FROM语句。我们首先需要知道最开始从哪个表开始的,这就是FROM告诉我们的。现在有了<left_table><right_table>两个表,我们到底从哪个表开始,还是从两个表进行某种联系以后再开始呢?它们之间如何产生联系呢?——笛卡尔积

关于什么是笛卡尔积,请自行Google补脑。经过FROM语句对两个表执行笛卡尔积,会得到一个虚拟表,暂且叫VT1(vitual table 1),内容如下:

+-------------+----------+----------+-------------+
| customer_id | city     | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163         | hangzhou |        1 | 163         |
| 9you        | shanghai |        1 | 163         |
| baidu       | hangzhou |        1 | 163         |
| tx          | hangzhou |        1 | 163         |
| 163         | hangzhou |        2 | 163         |
| 9you        | shanghai |        2 | 163         |
| baidu       | hangzhou |        2 | 163         |
| tx          | hangzhou |        2 | 163         |
| 163         | hangzhou |        3 | 9you        |
| 9you        | shanghai |        3 | 9you        |
| baidu       | hangzhou |        3 | 9you        |
| tx          | hangzhou |        3 | 9you        |
| 163         | hangzhou |        4 | 9you        |
| 9you        | shanghai |        4 | 9you        |
| baidu       | hangzhou |        4 | 9you        |
| tx          | hangzhou |        4 | 9you        |
| 163         | hangzhou |        5 | 9you        |
| 9you        | shanghai |        5 | 9you        |
| baidu       | hangzhou |        5 | 9you        |
| tx          | hangzhou |        5 | 9you        |
| 163         | hangzhou |        6 | tx          |
| 9you        | shanghai |        6 | tx          |
| baidu       | hangzhou |        6 | tx          |
| tx          | hangzhou |        6 | tx          |
| 163         | hangzhou |        7 | NULL        |
| 9you        | shanghai |        7 | NULL        |
| baidu       | hangzhou |        7 | NULL        |
| tx          | hangzhou |        7 | NULL        |
+-------------+----------+----------+-------------+

总共有28(table1的记录条数 * table2的记录条数)条记录。这就是VT1的结果,接下来的操作就在VT1的基础上进行。

执行ON过滤

执行完笛卡尔积以后,接着就进行ON a.customer_id = b.customer_id条件过滤,根据ON中指定的条件,去掉那些不符合条件的数据,得到VT2表,内容如下:

+-------------+----------+----------+-------------+
| customer_id | city     | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163         | hangzhou |        1 | 163         |
| 163         | hangzhou |        2 | 163         |
| 9you        | shanghai |        3 | 9you        |
| 9you        | shanghai |        4 | 9you        |
| 9you        | shanghai |        5 | 9you        |
| tx          | hangzhou |        6 | tx          |
+-------------+----------+----------+-------------+

VT2就是经过ON条件筛选以后得到的有用数据,而接下来的操作将在VT2的基础上继续进行。

添加外部行

这一步只有在连接类型为OUTER JOIN时才发生,如LEFT OUTER JOINRIGHT OUTER JOINFULL OUTER JOIN。在大多数的时候,我们都是会省略掉OUTER关键字的,但OUTER表示的就是外部行的概念。

LEFT OUTER JOIN把左表记为保留表,得到的结果为:

+-------------+----------+----------+-------------+
| customer_id | city     | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163         | hangzhou |        1 | 163         |
| 163         | hangzhou |        2 | 163         |
| 9you        | shanghai |        3 | 9you        |
| 9you        | shanghai |        4 | 9you        |
| 9you        | shanghai |        5 | 9you        |
| tx          | hangzhou |        6 | tx          |
| baidu       | hangzhou |     NULL | NULL        |
+-------------+----------+----------+-------------+

RIGHT OUTER JOIN把右表记为保留表,得到的结果为:

+-------------+----------+----------+-------------+
| customer_id | city     | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163         | hangzhou |        1 | 163         |
| 163         | hangzhou |        2 | 163         |
| 9you        | shanghai |        3 | 9you        |
| 9you        | shanghai |        4 | 9you        |
| 9you        | shanghai |        5 | 9you        |
| tx          | hangzhou |        6 | tx          |
| NULL        | NULL     |        7 | NULL        |
+-------------+----------+----------+-------------+

FULL OUTER JOIN把左右表都作为保留表,得到的结果为:

+-------------+----------+----------+-------------+
| customer_id | city     | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163         | hangzhou |        1 | 163         |
| 163         | hangzhou |        2 | 163         |
| 9you        | shanghai |        3 | 9you        |
| 9you        | shanghai |        4 | 9you        |
| 9you        | shanghai |        5 | 9you        |
| tx          | hangzhou |        6 | tx          |
| baidu       | hangzhou |     NULL | NULL        |
| NULL        | NULL     |        7 | NULL        |
+-------------+----------+----------+-------------+

添加外部行的工作就是在VT2表的基础上添加保留表中被过滤条件过滤掉的数据,非保留表中的数据被赋予NULL值,最后生成虚拟表VT3。

由于我在准备的测试SQL查询逻辑语句中使用的是LEFT JOIN,过滤掉了以下这条数据:

| baidu       | hangzhou |     NULL | NULL        |

现在就把这条数据添加到VT2表中,得到的VT3表如下:

+-------------+----------+----------+-------------+
| customer_id | city     | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163         | hangzhou |        1 | 163         |
| 163         | hangzhou |        2 | 163         |
| 9you        | shanghai |        3 | 9you        |
| 9you        | shanghai |        4 | 9you        |
| 9you        | shanghai |        5 | 9you        |
| tx          | hangzhou |        6 | tx          |
| baidu       | hangzhou |     NULL | NULL        |
+-------------+----------+----------+-------------+

接下来的操作都会在该VT3表上进行。

执行WHERE过滤

对添加外部行得到的VT3进行WHERE过滤,只有符合<where_condition>的记录才会输出到虚拟表VT4中。当我们执行WHERE a.city = 'hangzhou'的时候,就会得到以下内容,并存在虚拟表VT4中:

+-------------+----------+----------+-------------+
| customer_id | city     | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163         | hangzhou |        1 | 163         |
| 163         | hangzhou |        2 | 163         |
| tx          | hangzhou |        6 | tx          |
| baidu       | hangzhou |     NULL | NULL        |
+-------------+----------+----------+-------------+

但是在使用WHERE子句时,需要注意以下两点:

  1. 由于数据还没有分组,因此现在还不能在WHERE过滤器中使用where_condition=MIN(col)这类对分组统计的过滤;
  2. 由于还没有进行列的选取操作,因此在SELECT中使用列的别名也是不被允许的,如:SELECT city as c FROM t WHERE c='shanghai';是不允许出现的。

执行GROUP BY分组

GROU BY子句主要是对使用WHERE子句得到的虚拟表进行分组操作。我们执行测试语句中的GROUP BY a.customer_id,就会得到以下内容:

+-------------+----------+----------+-------------+
| customer_id | city     | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163         | hangzhou |        1 | 163         |
| baidu       | hangzhou |     NULL | NULL        |
| tx          | hangzhou |        6 | tx          |
+-------------+----------+----------+-------------+

得到的内容会存入虚拟表VT5中,此时,我们就得到了一个VT5虚拟表,接下来的操作都会在该表上完成。

执行HAVING过滤

HAVING子句主要和GROUP BY子句配合使用,对分组得到的VT5虚拟表进行条件过滤。当我执行测试语句中的HAVING count(b.order_id) < 2时,将得到以下内容:

+-------------+----------+----------+-------------+
| customer_id | city     | order_id | customer_id |
+-------------+----------+----------+-------------+
| baidu       | hangzhou |     NULL | NULL        |
| tx          | hangzhou |        6 | tx          |
+-------------+----------+----------+-------------+

这就是虚拟表VT6。

SELECT列表

现在才会执行到SELECT子句,不要以为SELECT子句被写在第一行,就是第一个被执行的。

我们执行测试语句中的SELECT a.customer_id, COUNT(b.order_id) as total_orders,从虚拟表VT6中选择出我们需要的内容。我们将得到以下内容:

+-------------+--------------+
| customer_id | total_orders |
+-------------+--------------+
| baidu       |            0 |
| tx          |            1 |
+-------------+--------------+

不,还没有完,这只是虚拟表VT7。

执行DISTINCT子句

如果在查询中指定了DISTINCT子句,则会创建一张内存临时表(如果内存放不下,就需要存放在硬盘了)。这张临时表的表结构和上一步产生的虚拟表VT7是一样的,不同的是对进行DISTINCT操作的列增加了一个唯一索引,以此来除重复数据。

由于我的测试SQL语句中并没有使用DISTINCT,所以,在该查询中,这一步不会生成一个虚拟表。

执行ORDER BY子句

对虚拟表中的内容按照指定的列进行排序,然后返回一个新的虚拟表,我们执行测试SQL语句中的ORDER BY total_orders DESC,就会得到以下内容:

+-------------+--------------+
| customer_id | total_orders |
+-------------+--------------+
| tx          |            1 |
| baidu       |            0 |
+-------------+--------------+

可以看到这是对total_orders列进行降序排列的。上述结果会存储在VT8中。

执行LIMIT子句

LIMIT子句从上一步得到的VT8虚拟表中选出从指定位置开始的指定行数据。对于没有应用ORDER BY的LIMIT子句,得到的结果同样是无序的,所以,很多时候,我们都会看到LIMIT子句会和ORDER BY子句一起使用。

MySQL数据库的LIMIT支持如下形式的选择:

LIMIT n, m

表示从第n条记录开始选择m条记录。而很多开发人员喜欢使用该语句来解决分页问题。对于小数据,使用LIMIT子句没有任何问题,当数据量非常大的时候,使用LIMIT n, m是非常低效的。因为LIMIT的机制是每次都是从头开始扫描,如果需要从第60万行开始,读取3条数据,就需要先扫描定位到60万行,然后再进行读取,而扫描的过程是一个非常低效的过程。所以,对于大数据处理时,是非常有必要在应用层建立一定的缓存机制(貌似现在的大数据处理,都有缓存哦)。各位,请期待我的缓存方面的文章哦。

总结

文章略长,但都是干货。仔细阅读完,肯定有收获的。好歹是总结完了,个人认为还是比书上的内容清晰一点,好懂一点。如果觉的文章不错,对你有帮助,你也可以打赏我。你也可以加果冻想的微信公众号,期待你与我交流。

2015年1月17日 于深圳。

===修改日志===

2015年8月26日 补上了SQL语句的执行顺序中的第六步。

MySQL与PHP“联姻”

果冻想阅读(773)评论(0)

从这里开始

我的博客,后台数据库是什么?没错,就是MySQL,服务器端使用的脚本就是PHP,整个框架使用的是WordPress。PHP和MySQL就像夫妻一样,总是在一起干活。现在这里,就结合PHP,总结一下MySQL的实际使用,也算作是MySQL开发的入门。关于PHP与MySQL的合作,不外乎以下三种方法:

  1. mysql扩展;但是目前已经不推荐使用;
  2. mysqli扩展;同时提供面向对象风格和面向过程的风格;要求MySQL版本是4.1及以上的;
  3. PDO扩展为PHP访问数据库定义了一种轻量级的一致接口;PDO_MYSQL是对其的具体实现。这里暂时只关心开发。由于mysql扩展已经不推荐使用了,我也会与时俱进,不做总结;而mysqli和PDO方式用的比较多,所以这篇将会总结如何使用mysqli扩展来连接数据库服务器,如何查询和获取数据,以及如何执行其它重要任务。下一篇博文将会总结PDO的相关内容。

使用mysqli扩展

先看以下测试数据库db_test中的测试数据:

mysql> select * from tb_test;
+----+-----------+----------+------------+------------+
| id | firstname | lastname | email      | phone      |
+----+-----------+----------+------------+------------+
|  1 | Young     | Jelly    | 123@qq.com | 1384532120 |
|  3 | Fang      | Jone     | 456@qq.com | 1385138913 |
|  4 | Yuan      | Su       | 789@qq.com | 1385138913 |
+----+-----------+----------+------------+------------+
3 rows in set (0.00 sec)
  1.  建立和断开连接
    与MySQL数据库交互时,首先要建立连接,最后要断开连接;这包括与服务器连接并选择一个数据库,以及最后关闭连接,释放资源。选择使用面向对象接口与MySQL服务器交互,首先需要通过其构造函数实例化mysqli类。

    <?php
        // 实例化mysqli类
        $mysqliConn = new mysqli();
        // 连接服务器,并选择一个数据库
        $mysqliConn->connect('127.0.0.1', 'root', 'root', 'db_test');
        printf("MySQL error number:%d", $mysqliConn->errno);
        // 或者
        // $mysqliConn->connect("http://127.0.0.1", 'root', 'root');
        // $mysqliConn->select_db('db_test');
        
        // 与数据库交互
        
        // 关闭连接
        $mysqliConn->close();
    ?>
    一旦成功的选择了数据库,然后就可以对这个数据库执行数据库查询了。一旦脚本执行完毕,所有打开的数据库连接都会自动关闭,并释放资源。不过,有可能一个页面在执行期间需要多个数据库连接,各个连接都应当适当的加以关闭。即使只使用一个连接,也应该在脚本的最后将其关闭,这是一种很好的实践方法。在任何情况下,都由close()负责关闭连接。
  2. 处理连接错误
    当然,如果无法连接MySQL数据库,那么不大可能在这个页面继续完成预期的工作了。因此,一定要注意监视连接错误并相应地做出反应。mysqli扩展包包含很多可以用来捕获错误消息的特性,另外也可以使用异常来做到这一点。例如,可以使用mysqli_connect_errno()和mysqli_connect_error()方法诊断并显示一个MySQL连接错误的有关信息。
    关于mysqli的具体信息可以在这里查看:http://php.net/manual/zh/book.mysqli.php

与数据库交互

绝大多数查询都与创建、获取、更新和删除任务有关,这些任务统称为CRUD。这里就开始总结CRUD相关的内容。

  1. 向数据库发送查询
    方法query()负责将query发送给数据库。它的定义如下:

    mixed mysqli::query ( string $query [, int $resultmode = MYSQLI_STORE_RESULT ] )
    可选参数resultmode可以用于修改这个方法的行为,它接受两个可取值。这篇文章总结了二者之间的区别。http://blog.csdn.net/robertaqi/article/details/6068087;下面是一个简单的使用例子:
    <?php
        // 实例化mysqli类
        $mysqliConn = new mysqli();
    
        // 连接服务器,并选择一个数据库
        // 错误的密码
        $mysqliConn->connect('127.0.0.1', 'root', 'root', 'db_test');
        if ($mysqliConn->connect_error)
        {
            printf("Unable to connect to the database:%s", $mysqliConn->connect_error);
            exit();
        }
        
        // 与数据库交互
        $query = 'select firstname, lastname, email from tb_test;';
    
        // 发送查询给MySQL
        $result = $mysqliConn->query($query);
    
        // 迭代处理结果集
        while (list($firstname, $lastname, $email) = $result->fetch_row())
        {
            printf("%s %s's email:%s<br/>", $firstname, $lastname, $email);
        }
        
        // 关闭连接
        $mysqliConn->close();
    ?>
  2. 插入、更新和删除数据
    插入、更新和删除使用的是insert、update和delete查询完成的,其做法实际上与select查询相同。示例代码如下:

    <?php
        // 实例化mysqli类
        $mysqliConn = new mysqli();
    
        // 连接服务器,并选择一个数据库
        // 错误的密码
        $mysqliConn->connect('127.0.0.1', 'root', 'root', 'db_test');
        if ($mysqliConn->connect_error)
        {
            printf("Unable to connect to the database:%s", $mysqliConn->connect_error);
            exit();
        }
        
        // 与数据库交互
        $query = 'select firstname, lastname, email from tb_test;';
        // 发送查询给MySQL
        $result = $mysqliConn->query($query);
    
        // 迭代处理结果集
        while (list($firstname, $lastname, $email) = $result->fetch_row())
        {
            printf("%s %s's email:%s<br/>", $firstname, $lastname, $email);
        }
        
        $query = "delete from tb_test where firstname = 'Yuan';";
        $result = $mysqliConn->query($query);
    
        // 告诉用户影响了多少行
        printf("%d row(s) have been deleted.<br/>", $mysqliConn->affected_rows);
        // 重新查询结果集
        $query = 'select firstname, lastname, email from tb_test;';
    
        // 发送查询给MySQL
        $result = $mysqliConn->query($query);
    
        // 迭代处理结果集
        while (list($firstname, $lastname, $email) = $result->fetch_row())
        {
            printf("%s %s's email:%s<br/>", $firstname, $lastname, $email);
        }
        // 关闭连接
        $mysqliConn->close();
    ?>
  3. 释放查询内存
    有时可能会获取一个特别庞大的结果集,此时一旦完成处理,很有必要释放该结果集所请求的内存。free()方法可以为我们完成这个任务。例如:

    // 与数据库交互
    $query = 'select firstname, lastname, email from tb_test;';
    
    // 发送查询给MySQL
    $result = $mysqliConn->query($query);
    
    // 迭代处理结果集
    while (list($firstname, $lastname, $email) = $result->fetch_row())
    {
        printf("%s %s's email:%s<br/>", $firstname, $lastname, $email);
    }
    $result->free();
  4. 解析查询结果
    一旦执行了查询并准备好结果集,下面就可以解析获取到的结果行了。你可以使用多个方法来获取各行中的字段,具体选择哪一个方法主要取决于个人喜好,因为只是引用字段的方法有所不同。

    (1)将结果放到对象中

    使用fetch_object()方法来完成。fetch_object()方法通常在一个循环中调用,每次调用都使得返回结果集中的下一行被填入一个对象,然后可以按照PHP典型的对象访问语法来访问这个对象。例如:

    // 与数据库交互
    $query = 'select firstname, lastname, email from tb_test;';
    
    // 发送查询给MySQL
    $result = $mysqliConn->query($query);
    
    // 迭代处理结果集
    while ($row = $result->fetch_object())
    {
        $firstname = $row->firstname;
        $lastname = $row->lastname;
        $email = $row->email;
    }
    $result->free();
    (2)使用索引数组和关联数组获取结果
    mysqli扩展包还允许通过fetch_array()方法和fetch_row()方法分别使用关联数组和索引数组来管理结果集。fetch_array()方法实际上能够将结果集的各行获取为一个关联数组、一个数字索引数组,或者同时包括二者,可以说,fetch_row()是fetch_array的一个子集。默认地,fetch_array()会同时获取关联数组和索引数组,可以在fetch_array中传入参数来修改这个默认行为。
    MYSQLI_ASSOC,将行作为一个关联数组返回,键由字段名表示,值由字段内容表示;
    MYSQLI_NUM,将行作为一个数字索引数组返回,其元素顺序由查询中指定的字段名顺序决定;
    MYSQLI_BOTH,就是默认的选项。
  5. 确定所选择的行和受影响的行
    通常希望能够确定select查询返回的行数,或者受insert、update或delete影响的行数。
    (1)确定返回的行数
    如果希望了解select查询语句返回了多少行,num_rows属性很有用。例如:

    // 与数据库交互
    $query = 'select firstname, lastname, email from tb_test;';
    
    // 发送查询给MySQL
    $result = $mysqliConn->query($query);
    
    // 获取行数
    $result->num_rows;
    记住,num_rows只在确定select查询所获取的行数时有用,如果要获得受insert、update或delete影响的行数,就要使用下面总结的affected_rows属性。

    (2)确定受影响的行数
    affected_rows属性用来获取受insert、update或delete影响的行数。代码示例见上面的代码。

执行数据库事务

有3个新方法增强了PHP执行MySQL事务的功能,分别为:

  1. autocommit函数,启用自动提交模式;
    autocommit()函数控制MySQL自动提交模式的行为,由传入的参数决定启动还是禁用自动提交;传入TRUE,则启动自动提交,传入false则禁用自动提交。无论启用还是禁用,成功时都将返回TRUE,失败时返回FALSE。
  2. commit函数,提交事务;将当前事务提交给数据库,成功时返回TRUE,否则返回FALSE。
  3. rollback函数,回滚当前事务,成功时返回TRUE,否则返回FALSE。

关于事务,我后面还要继续总结,这里就简要的总结了一下这三个API。

不会结束

这只是MySQL学习的开始,不会结束。再接再励。

2014年9月20日 于深圳。

MySQL处理数据库和表的常用命令

果冻想阅读(1300)评论(3)

我是新手

学习如何管理和导航MySQL数据库和表是要掌握的首要任务之一,下面的内容将主要对MySQL的数据库和表的一些常用命令进行总结,一些我们不得不掌握的命令,一些信手拈来的命令。

处理数据库

  1. 查看数据库
    获取服务器上的数据库列表通常很有用。执行show databases;命令就可以搞定。

    mysql> show databases;
  2. 创建数据库
    mysql> create database db_test;
    Query OK, 1 row affected (0.00 sec)
  3. 使用数据库
    数据库一旦创建,就可以通过“使用”(use命令)数据库,将其指定为默认的工作数据库。

    mysql> use db_test;
    Database changed
  4. 删除数据库
    删除数据库的方式与创建的方式很相似。可以在mysql客户端中使用drop命令删除数据库,如下:

    mysql> drop database db_test;
    Query OK, 0 rows affected (0.00 sec)

处理表

这里将对如何创建、列出、查看、删除和修改MySQL数据库表。

  1. 创建表
    表通过create table语句来创建。创建表的过程中会使用非常多的选项和子句,在这里完全总结一遍也是不现实的,这里只是总结最普遍的,以后遇到别的,再单个总结。创建表的一般用法如下:

    mysql> create table tb_test(
        -> id int unsigned not null auto_increment,
        -> firstname varchar(25) not null,
        -> lastname varchar(25) not null,
        -> email varchar(45) not null,
        -> phone varchar(10) not null,
        -> primary key(id));
    Query OK, 0 rows affected (0.03 sec)
    记住,表至少包含一列。另外,创建表之后总是可以再回过头来修改表的结构。无论当前是否在使用目标数据库,都可以创建表,只要在表名前面加上目标数据库即可。例如:
    mysql> create table db_test.tb_test(
        -> id int unsigned not null auto_increment,
        -> firstname varchar(25) not null,
        -> lastname varchar(25) not null,
        -> email varchar(45) not null,
        -> phone varchar(10) not null,
        -> primary key(id));
    Query OK, 0 rows affected (0.03 sec)
  2. 有条件的创建表
    在默认情况下,如果试图创建一个已经存在的表,MySQL会产生一个错误。为了避免这个错误,create table语句提供了一个子句,如果你希望在目标表已经存在的情况下简单地退出表创建,就可以使用这个子句。例如:

    mysql> create table if not exists db_test.tb_test(
        -> id int unsigned not null auto_increment,
        -> firstname varchar(25) not null,
        -> lastname varchar(25) not null,
        -> email varchar(45) not null,
        -> phone varchar(10) not null,
        -> primary key(id));
    Query OK, 0 rows affected, 1 warning (0.00 sec)

    无论是否已经创建,都会在返回到命令提示窗口时显示“Query OK”消息。

  3. 复制表
    基于现有的表创建新表是一项很容易的任务。以下代码将得到tb_test表的一个副本,名为tb_test2:

    mysql> create table tb_test2 select * from db_test.tb_test;
    Query OK, 0 rows affected (0.03 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    将向数据库增加一个相同的表tb_test2。而有的时候,可能希望只基于现有表的几个列创建一个表。通过create select语句中指定列就可以实现:
    mysql> describe tb_test;
    +-----------+------------------+------+-----+---------+----------------+
    | Field     | Type             | Null | Key | Default | Extra          |
    +-----------+------------------+------+-----+---------+----------------+
    | id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
    | firstname | varchar(25)      | NO   |     | NULL    |                |
    | lastname  | varchar(25)      | NO   |     | NULL    |                |
    | email     | varchar(45)      | NO   |     | NULL    |                |
    | phone     | varchar(10)      | NO   |     | NULL    |                |
    +-----------+------------------+------+-----+---------+----------------+
    5 rows in set (0.01 sec)
    mysql> create table tb_test2 select id, firstname, lastname, email from tb_test;
    Query OK, 0 rows affected (0.03 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    mysql> describe tb_test2;
    +-----------+------------------+------+-----+---------+-------+
    | Field     | Type             | Null | Key | Default | Extra |
    +-----------+------------------+------+-----+---------+-------+
    | id        | int(10) unsigned | NO   |     | 0       |       |
    | firstname | varchar(25)      | NO   |     | NULL    |       |
    | lastname  | varchar(25)      | NO   |     | NULL    |       |
    | email     | varchar(45)      | NO   |     | NULL    |       |
    +-----------+------------------+------+-----+---------+-------+
    4 rows in set (0.01 sec)
  4. 创建临时表
    有的时候,当工作在非常大的表上时,可能偶尔需要运行很多查询获得一个大量数据的小的子集,不是对整个表运行这些查询,而是让MySQL每次找出所需的少数记录,将记录保存到一个临时表可能更快一些,然后对这些临时表进行查询操作。可以通过使用temporary关键字和create table语句来实现。

    mysql> create temporary table emp_temp select firstname, lastname from tb_test;
    Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    临时表的创建与其它表一样,只是它们存储在操作系统指定的临时目录中。临时表将在你连接MySQL期间存在,当你断开时,MySQL将自动删除表并释放所有的内存空间;当然了,你也可以手动的使用drop table命令删除临时表。
  5. 查看数据库中可用的表
    可以使用show tables命令完成。例如:

    mysql> show tables;
    +-------------------+
    | Tables_in_db_test |
    +-------------------+
    | tb_test           |
    | tb_test2          |
    +-------------------+
    2 rows in set (0.00 sec)
  6. 查看表结构
    可以使用describe语句查看表结构,例如:

    mysql> describe tb_test;
    +-----------+------------------+------+-----+---------+----------------+
    | Field     | Type             | Null | Key | Default | Extra          |
    +-----------+------------------+------+-----+---------+----------------+
    | id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
    | firstname | varchar(25)      | NO   |     | NULL    |                |
    | lastname  | varchar(25)      | NO   |     | NULL    |                |
    | email     | varchar(45)      | NO   |     | NULL    |                |
    | phone     | varchar(10)      | NO   |     | NULL    |                |
    +-----------+------------------+------+-----+---------+----------------+
    5 rows in set (0.00 sec)
    另外,使用show命令也能得到相同的结果,例如:
    mysql> show columns in tb_test;
    +-----------+------------------+------+-----+---------+----------------+
    | Field     | Type             | Null | Key | Default | Extra          |
    +-----------+------------------+------+-----+---------+----------------+
    | id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
    | firstname | varchar(25)      | NO   |     | NULL    |                |
    | lastname  | varchar(25)      | NO   |     | NULL    |                |
    | email     | varchar(45)      | NO   |     | NULL    |                |
    | phone     | varchar(10)      | NO   |     | NULL    |                |
    +-----------+------------------+------+-----+---------+----------------+
    5 rows in set (0.00 sec)
  7. 删除表
    删除表是使用drop table语句实现的,其语法如下:

    drop [temporary] table [if exists] tbl_name [, tbl_name, ...]
  8. 更改表结构
    我们会发现,我们会经常修改和改进表结构,特别是在开发初期;但是,每次进行修改时不必都先删除再重新创建表。相反,可以使用alter语句修改表的结构。利用这个语句,可以再必要时删除、修改和增加列。和create table一样,alter table提供了很多子句、关键字和选项。这里只是会说一些简单的使用,比如在表tb_demo表中插入一列,表示email,代码如下:

    mysql> alter table tb_demo add column email varchar(45);
    Query OK, 0 rows affected (0.14 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    新的列放在表的最后位置。不过,还可以使用适当的关键字(包括first、after和last)来控制新列的位置。如果想修改表,比如,刚刚加的email,我想加入一个not null控制,代码可以是这样的:
    mysql> alter table tb_demo change email email varchar(45) not null;
    Query OK, 0 rows affected (0.11 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    如果觉的这个email这列没有存在的必要了,可以使用下面的代码删除它,例如:
    mysql> alter table tb_demo drop email;
    Query OK, 0 rows affected (0.09 sec)
    Records: 0  Duplicates: 0  Warnings: 0

我不是新手

这篇文章大体上总结了与MySQL打交道时常用的一些命令,希望对大家有帮助。看完这篇文章,你应该认为你已经不是新手了,如果上面的命令你都实践过一遍以后,你应该比60%的人都熟悉MySQL数据库。就是这样,越简单的东西,越是有很多人不会。

2014年9月19日 于深圳。

MySQL数据类型和属性

果冻想阅读(1523)评论(0)

前言

好比C++中,定义int类型需要多少字节,定义double类型需要多少字节一样,MySQL对表每个列中的数据也会实行严格控制,这是数据驱动应用程序成功的关键。MySQL提供了一组可以赋给表中各个列的数据类型,每个类型都强制数据满足为该数据类型预先确定的一组规则,例如大小、类型及格式。

这里先总结数据类型。MySQL中的数据类型大的方面来分,可以分为:日期和时间、数值,以及字符串。下面就分开来进行总结。

日期和时间数据类型

MySQL数据类型 含义
date 3字节,日期,格式:2014-09-18
time 3字节,时间,格式:08:42:30
datetime 8字节,日期时间,格式:2014-09-18 08:42:30
timestamp 4字节,自动存储记录修改的时间
year 1字节,年份

数值数据类型

整型

MySQL数据类型 含义(有符号)
tinyint 1字节,范围(-128~127)
smallint 2字节,范围(-32768~32767)
mediumint 3字节,范围(-8388608~8388607)
int 4字节,范围(-2147483648~2147483647)
bigint 8字节,范围(+-9.22*10的18次方)

上面定义的都是有符号的,当然了,也可以加上unsigned关键字,定义成无符号的类型,那么对应的取值范围就要翻翻了,比如:

tinyint unsigned的取值范围为0~255。

浮点型

MySQL数据类型 含义
float(m, d) 4字节,单精度浮点型,m总个数,d小数位
double(m, d) 8字节,双精度浮点型,m总个数,d小数位
decimal(m, d) decimal是存储为字符串的浮点数

我在MySQL中建立了一个表,有一列为float(5, 3);做了以下试验:

  1. 插入123.45678,最后查询得到的结果为99.999;
  2. 插入123.456,最后查询结果为99.999;
  3. 插入12.34567,最后查询结果为12.346;

所以,在使用浮点型的时候,还是要注意陷阱的,要以插入数据库中的实际结果为准。

字符串数据类型

MySQL数据类型 含义
char(n) 固定长度,最多255个字符
varchar(n) 可变长度,最多65535个字符
tinytext 可变长度,最多255个字符
text 可变长度,最多65535个字符
mediumtext 可变长度,最多2的24次方-1个字符
longtext 可变长度,最多2的32次方-1个字符
  1. char(n)和varchar(n)中括号中n代表字符的个数,并不代表字节个数,所以当使用了中文的时候(UTF8)意味着可以插入m个中文,但是实际会占用m*3个字节。
  2. 同时char和varchar最大的区别就在于char不管实际value都会占用n个字符的空间,而varchar只会占用实际字符应该占用的空间+1,并且实际空间+1<=n。
  3. 超过char和varchar的n设置后,字符串会被截断。
  4. char的上限为255字节,varchar的上限65535字节,text的上限为65535。
  5. char在存储的时候会截断尾部的空格,varchar和text不会。
  6. varchar会使用1-3个字节来存储长度,text不会。

其它类型

  1. enum(“member1”, “member2”,  … “member65535”)
    enum数据类型就是定义了一种枚举,最多包含65535个不同的成员。当定义了一个enum的列时,该列的值限制为列定义中声明的值。如果列声明包含NULL属性,则NULL将被认为是一个有效值,并且是默认值。如果声明了NOT NULL,则列表的第一个成员是默认值。
  2. set(“member”, “member2”, … “member64”)
    set数据类型为指定一组预定义值中的零个或多个值提供了一种方法,这组值最多包括64个成员。值的选择限制为列定义中声明的值。

数据类型属性

上面大概总结了MySQL中的数据类型,当然了,上面的总结肯定是不全面的,如果要非常全面的总结这些内容,好几篇文章都不够的。下面就再来总结一些常用的属性。

  1. auto_increment
    auto_increment能为新插入的行赋一个唯一的整数标识符。为列赋此属性将为每个新插入的行赋值为上一次插入的ID+1。
    MySQL要求将auto_increment属性用于作为主键的列。此外,每个表只允许有一个auto_increment列。例如:

    id smallint not null auto_increment primary key
  2. binary
    binary属性只用于char和varchar值。当为列指定了该属性时,将以区分大小写的方式排序。与之相反,忽略binary属性时,将使用不区分大小写的方式排序。例如:

    hostname char(25) binary not null
  3. default
    default属性确保在没有任何值可用的情况下,赋予某个常量值,这个值必须是常量,因为MySQL不允许插入函数或表达式值。此外,此属性无法用于BLOB或TEXT列。如果已经为此列指定了NULL属性,没有指定默认值时默认值将为NULL,否则默认值将依赖于字段的数据类型。例如:

    subscribed enum('0', '1') not null default '0'
  4. index
    如果所有其他因素都相同,要加速数据库查询,使用索引通常是最重要的一个步骤。索引一个列会为该列创建一个有序的键数组,每个键指向其相应的表行。以后针对输入条件可以搜索这个有序的键数组,与搜索整个未索引的表相比,这将在性能方面得到极大的提升。

    create table employees
    (
        id varchar(9) not null,
        firstname varchar(15) not null,
        lastname varchar(25) not null,
        email varchar(45) not null,
        phone varchar(10) not null,
        index lastname(lastname),
        primary key(id)
    );
    我们也可以利用MySQL的create index命令在创建表之后增加索引:
    create index lastname on employees (lastname(7));
    这一次只索引了名字的前7个字符,因为可能不需要其它字母来区分不同的名字。因为使用较小的索引时性能更好,所以应当在实践中尽量使用小的索引。
  5. not null
    如果将一个列定义为not null,将不允许向该列插入null值。建议在重要情况下始终使用not null属性,因为它提供了一个基本验证,确保已经向查询传递了所有必要的值。
  6. null
    为列指定null属性时,该列可以保持为空,而不论行中其它列是否已经被填充。记住,null精确的说法是“无”,而不是空字符串或0。
  7. primary key
    primary key属性用于确保指定行的唯一性。指定为主键的列中,值不能重复,也不能为空。为指定为主键的列赋予auto_increment属性是很常见的,因为此列不必与行数据有任何关系,而只是作为一个唯一标识符。主键又分为以下两种:
    (1)单字段主键
    如果输入到数据库中的每行都已经有不可修改的唯一标识符,一般会使用单字段主键。注意,此主键一旦设置就不能再修改。
    (2)多字段主键
    如果记录中任何一个字段都不可能保证唯一性,就可以使用多字段主键。这时,多个字段联合起来确保唯一性。如果出现这种情况,指定一个auto_increment整数作为主键是更好的办法。
  8. unique
    被赋予unique属性的列将确保所有值都有不同的值,只是null值可以重复。一般会指定一个列为unique,以确保该列的所有值都不同。例如:

    email varchar(45) unique
  9. zerofill
    zerofill属性可用于任何数值类型,用0填充所有剩余字段空间。例如,无符号int的默认宽度是10;因此,当“零填充”的int值为4时,将表示它为0000000004。例如:

    orderid int unsigned zerofill not null

总结完毕!!!

九·一八,在大连,还能听到防空警报,在深圳怎么就没有呢?

2014年9月18日 于深圳。

MySQL存储引擎介绍

果冻想阅读(1904)评论(6)

前言

在数据库中存的就是一张张有着千丝万缕关系的表,所以表设计的好坏,将直接影响着整个数据库。而在设计表的时候,我们都会关注一个问题,使用什么存储引擎。等一下,存储引擎?什么是存储引擎?

什么是存储引擎?

关系数据库表是用于存储和组织信息的数据结构,可以将表理解为由行和列组成的表格,类似于Excel的电子表格的形式。有的表简单,有的表复杂,有的表根本不用来存储任何长期的数据,有的表读取时非常快,但是插入数据时去很差;而我们在实际开发过程中,就可能需要各种各样的表,不同的表,就意味着存储不同类型的数据,数据的处理上也会存在着差异,那么。对于MySQL来说,它提供了很多种类型的存储引擎,我们可以根据对数据处理的需求,选择不同的存储引擎,从而最大限度的利用MySQL强大的功能。这篇博文将总结和分析各个引擎的特点,以及适用场合,并不会纠结于更深层次的东西。我的学习方法是先学会用,懂得怎么用,再去知道到底是如何能用的。下面就对MySQL支持的存储引擎进行简单的介绍。

MyISAM

在mysql客户端中,使用以下命令可以查看MySQL支持的引擎。

show engines;

MyISAM表是独立于操作系统的,这说明可以轻松地将其从Windows服务器移植到Linux服务器;每当我们建立一个MyISAM引擎的表时,就会在本地磁盘上建立三个文件,文件名就是表名。例如,我建立了一个MyISAM引擎的tb_Demo表,那么就会生成以下三个文件:

  1. tb_demo.frm,存储表定义;
  2. tb_demo.MYD,存储数据;
  3. tb_demo.MYI,存储索引。

MyISAM表无法处理事务,这就意味着有事务处理需求的表,不能使用MyISAM存储引擎。MyISAM存储引擎特别适合在以下几种情况下使用:

  1. 选择密集型的表。MyISAM存储引擎在筛选大量数据时非常迅速,这是它最突出的优点。
  2. 插入密集型的表。MyISAM的并发插入特性允许同时选择和插入数据。例如:MyISAM存储引擎很适合管理邮件或Web服务器日志数据。

InnoDB

InnoDB是一个健壮的事务型存储引擎,这种存储引擎已经被很多互联网公司使用,为用户操作非常大的数据存储提供了一个强大的解决方案。我的电脑上安装的MySQL 5.6.13版,InnoDB就是作为默认的存储引擎。InnoDB还引入了行级锁定和外键约束,在以下场合下,使用InnoDB是最理想的选择:

  1. 更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。
  2. 事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。
  3. 自动灾难恢复。与其它存储引擎不同,InnoDB表能够自动从灾难中恢复。
  4. 外键约束。MySQL支持外键的存储引擎只有InnoDB。
  5. 支持自动增加列AUTO_INCREMENT属性。

一般来说,如果需要事务支持,并且有较高的并发读取频率,InnoDB是不错的选择。

MEMORY

使用MySQL Memory存储引擎的出发点是速度。为得到最快的响应时间,采用的逻辑存储介质是系统内存。虽然在内存中存储表数据确实会提供很高的性能,但当mysqld守护进程崩溃时,所有的Memory数据都会丢失。获得速度的同时也带来了一些缺陷。它要求存储在Memory数据表里的数据使用的是长度不变的格式,这意味着不能使用BLOB和TEXT这样的长度可变的数据类型,VARCHAR是一种长度可变的类型,但因为它在MySQL内部当做长度固定不变的CHAR类型,所以可以使用。

一般在以下几种情况下使用Memory存储引擎:

  1. 目标数据较小,而且被非常频繁地访问。在内存中存放数据,所以会造成内存的使用,可以通过参数max_heap_table_size控制Memory表的大小,设置此参数,就可以限制Memory表的最大大小。
  2. 如果数据是临时的,而且要求必须立即可用,那么就可以存放在内存表中。
  3. 存储在Memory表中的数据如果突然丢失,不会对应用服务产生实质的负面影响。

Memory同时支持散列索引和B树索引。B树索引的优于散列索引的是,可以使用部分查询和通配查询,也可以使用<、>和>=等操作符方便数据挖掘。散列索引进行“相等比较”非常快,但是对“范围比较”的速度就慢多了,因此散列索引值适合使用在=和<>的操作符中,不适合在<或>操作符中,也同样不适合用在order by子句中。

可以在表创建时利用USING子句指定要使用的版本。例如:

create table users
(
    id smallint unsigned not null auto_increment,
    username varchar(15) not null,
    pwd varchar(15) not null,
    index using hash (username),
    primary key (id)
)engine=memory;

上述代码创建了一个表,在username字段上使用了HASH散列索引。下面的代码就创建一个表,使用BTREE索引。

create table users
(
    id smallint unsigned not null auto_increment,
    username varchar(15) not null,
    pwd varchar(15) not null,
    index using btree (username),
    primary key (id)
)engine=memory;

MERGE

MERGE存储引擎是一组MyISAM表的组合,这些MyISAM表结构必须完全相同,尽管其使用不如其它引擎突出,但是在某些情况下非常有用。说白了,Merge表就是几个相同MyISAM表的聚合器;Merge表中并没有数据,对Merge类型的表可以进行查询、更新、删除操作,这些操作实际上是对内部的MyISAM表进行操作。Merge存储引擎的使用场景。

对于服务器日志这种信息,一般常用的存储策略是将数据分成很多表,每个名称与特定的时间端相关。例如:可以用12个相同的表来存储服务器日志数据,每个表用对应各个月份的名字来命名。当有必要基于所有12个日志表的数据来生成报表,这意味着需要编写并更新多表查询,以反映这些表中的信息。与其编写这些可能出现错误的查询,不如将这些表合并起来使用一条查询,之后再删除Merge表,而不影响原来的数据,删除Merge表只是删除Merge表的定义,对内部的表没有任何影响。

下面就通过一个简单的例子来说说如何建立引擎为merge类型的表。

create table tb_log1(
    id int unsigned not null auto_increment, 
    log varchar(45),
    primary key(id)) engine=myisam;

insert into tb_log1(log) values('tb_log1_1');
insert into tb_log1(log) values('tb_log1_2');
insert into tb_log1(log) values('tb_log1_3');
insert into tb_log1(log) values('tb_log1_4');
insert into tb_log1(log) values('tb_log1_5');

create table tb_log2(
    id int unsigned not null auto_increment,
    log varchar(45),
    primary key(id)) engine=myisam;

insert into tb_log2(log) values('tb_log2_1');
insert into tb_log2(log) values('tb_log2_2');
insert into tb_log2(log) values('tb_log2_3');
insert into tb_log2(log) values('tb_log2_4');

先创建两个引擎为myisam(必须为myisam引擎)的表。插入上述数据,然后创建merge表,进行merge操作。

create table tb_merge(
    id int unsigned not null auto_increment, 
    log varchar(45), 
    primary key(id))engine=merge 
    union(tb_log1,tb_log2) insert_method=last;

这样就得到了一个引擎为merge的表,并且合并了tb_log1和tb_log2两个表。查询tb_merge表,可以得到以下数据:

+----+-----------+
| id | log       |
+----+-----------+
|  1 | tb_log1_1 |
|  2 | tb_log1_2 |
|  3 | tb_log1_3 |
|  4 | tb_log1_4 |
|  5 | tb_log1_5 |
|  1 | tb_log2_1 |
|  2 | tb_log2_2 |
|  3 | tb_log2_3 |
|  4 | tb_log2_4 |
+----+-----------+

现在我们主要来解释一下上面MERGE表的建表语句。

  1. ENGINE=MERGE
    指明使用MERGE引擎,有些同学可能见到过ENGINE=MRG_MyISAM的例子,也是对的,它们是一回事。
  2. UNION=(t1, t2)
    指明了MERGE表中挂接了些哪表,可以通过alter table的方式修改UNION的值,以实现增删MERGE表子表的功能。比如:

     alter table tb_merge engine=merge union(tb_log1) insert_method=last;
  3. INSERT_METHOD=LAST
    INSERT_METHOD指明插入方式,取值可以是:0 不允许插入;FIRST 插入到UNION中的第一个表; LAST 插入到UNION中的最后一个表。
  4. MERGE表及构成MERGE数据表结构的各成员数据表必须具有完全一样的结构。每一个成员数据表的数据列必须按照同样的顺序定义同样的名字和类型,索引也必须按照同样的顺序和同样的方式定义。

ARCHIVE

Archive是归档的意思,在归档之后很多的高级功能就不再支持了,仅仅支持最基本的插入和查询两种功能。在MySQL 5.5版以前,Archive是不支持索引,但是在MySQL 5.5以后的版本中就开始支持索引了。Archive拥有很好的压缩机制,它使用zlib压缩库,在记录被请求时会实时压缩,所以它经常被用来当做仓库使用。

存储引擎的一些问题

  1. 如何查看服务器有哪些存储引擎可以使用?
    为确定你的MySQL服务器可以用哪些存储引擎,执行如下命令:

    show engines;
    这个命令就能搞定了。
  2. 如何选择合适的存储引擎?
    选择标准可以分为:
    (1)是否需要支持事务;
    (2)是否需要使用热备;
    (3)崩溃恢复:能否接受崩溃;
    (4)是否需要外键支持;
    然后按照标准,选择对应的存储引擎即可。

总结

这篇文章总结了几种比较常用的存储引擎,对于实际的工作,需要根据具体的情况而定,结合实际的项目实例进行应用,才是最好的学习方法。

2014年9月17日 于深圳。

===修改日志===

2014年11月7日 修改了个别错别字。

2015年1月21日 添加了merge引擎的实战例子。

MySQL扫盲篇

果冻想阅读(1772)评论(1)

为什么总结MySQL?

说实话,MySQL也用了一段时间了,从大学阶段开始就使用了,但是从来都没有系统的学习过,只是用,用的很糊涂,很多东西都用的糊里糊涂的,说实话,这种感觉是很空旷的,总是让人不踏实;而数据库有是一个IT人必备的技能,所以,从这篇文章起,我就选择以MySQL作为突破点,开始从最基本的开始,总结MySQL。虽然网上这方面的教程非常多,但是自己总结的东西,才会真正的称为自己的。

扫盲

MySQL的下载和安装,就不废话了,不管你是百度和是Google,这个你都可以自己搞定的。MySQL作为一个数据库,主要是由两部分组成,服务器端和客户端。简单的说,服务器端用来保存数据,客户端就是连接服务器端,对服务器进行各种操作的。所以,我们要对MySQL进行操作时,要先启动服务器端,然后再启动客户端去连接服务器端,进行各种数据库操作。

我们可以通过启动MySQL安装目录下Bin文件夹中的mysqld.exe来启动MySQL服务器程序,这个程序是一个守护进程,它会启动MySQL服务器;启动MySQL服务器以后,我们就可以通过MySQL客户端来连接服务器了,这里主要总结的是mysql.exe和mysqladmin.exe这两个客户端。既然启动了数据库服务器,那么也可以关闭的,关闭MySQL数据库服务器使用以下命令:

mysqladmin -u root -p shutdown;

mysqladmin是什么命令,稍后会讲到。

设置MySQL管理员密码

连接MySQL服务器需要密码,一般情况下,安装完MySQL的root用户的密码为空,所以,需要手动设置密码,命令如下:

mysql -h localhost -u root;
set password for root@localhost=password('newpassword');

先以空密码登陆MySQL,然后再使用上述命令设置新的密码。之后,再退出,再次连接服务器时,就需要输入密码。

mysql.exe客户端

MySQL提供了一些实用的客户端,现在就对比较常用的几个命令行客户端进行总结。

mysql.exe客户端是一个特别有用的客户端,几乎能够管理MySQL的每个方面:创建、修改和删除表和数据库;创建和管理用户;浏览和修改服务器配置;查询表数据等。

使用mysql客户端连接MySQL数据库的命令如下:

mysql -h localhost -u root -p

-h指定服务器所在的主机;
-u指定连接服务器的用户名;
-p指定密码连接服务器。

成功连接服务器以后,就可以对数据库进行各种操作了。

mysqladmin.exe客户端

mysqladmin客户端用于完成大量管理任务,其中最突出的可能是创建和删除数据库、监视服务器状态和关闭MySQL服务器守护进程。使用方式和mysql几乎一致,但是mysqladmin客户端的功能却比mysql少很多,一般都只是在特定情况下使用该客户端。

我使用的客户端

我在平时工作时,一般使用mysql.exe客户端;而使用的GUI客户端是Navicat for MySQL。这样就能满足我的日常工作了。

总结

这篇文章很简单,就是告诉大家,如何打开MySQL数据库,如果能从客户端访问MySQL数据库,这是一切学习的前提,所以这里先开头讲一下,后面的MySQL的博文,就开始总结MySQL的相关开发知识,基本不会总结MySQL的管理方面的知识,因为我也不会,工作中也没有涉及到MySQL的管理任务,基本都是基于MySQL的开发。

2014年9月15日 于深圳。

在这里玩技术,享受技术带来的疯狂

捐赠名单关于果冻