当DB2爱上MySQL：数据库特性对比（下）

当DB2爱上MySQL：数据库特性对比（下）

MySQL是目前最流行的开源数据库。由于其部署方便，运维简单，被广泛用于互联网的各个领域，目前随着整体IT架构的变更，传统的金融，电信业务，也开始逐渐从Oracle向MySQL迁移。在上一篇文章中，我们从体系结构、隔离级别、数据库约束、序列、表空间、XML等六个方面做了对比，本文我们将继续分析DB2与MySQL的数据库的差异。

数据库锁

数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。

加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。

对于该问题，我们的关注点有三个：是否支持行锁、是否存在行锁升级、是否支持MVCC。

MySQL的innodb存储引擎支持行锁，不需要行锁升级，支持MVCC。DB2支持行锁，存在行锁升级，部分支持MVCC。

如下表：

例1MySQL lock表和unlock表语法。

LOCK TABLES    tbl_name [[AS] alias] lock_type    [, tbl_name [[AS] alias] lock_type] ...lock_type:    READ [LOCAL]  | [LOW_PRIORITY] WRITEUNLOCK TABLES

数据导入

在数据库的日常运维中，我们进程面临需要将一个格式化的数据文本文件或sql文件导入数据库。我们的关注点主要有三方面：是否支持CSV文件、是否支持SQL、是否支持nolog。

MySQL支持CSV和SQL，但不建议使用nolog方式导入，因为MySQL有些功能必须依赖binlog方能实现，如果采用nolog方式，可能会对数据产生影响。

DB2支持CSV、SQL、nolog导入。

如下表：

例1MySQL load语法：

LOAD DATA [LOW_PRIORITY |CONCURRENT] [LOCAL] INFILE 'file_name'    [REPLACE | IGNORE]    INTO TABLE tbl_name    [PARTITION (partition_name,...)]    [CHARACTER SET charset_name]    [{FIELDS | COLUMNS}        [TERMINATED BY 'string']        [[OPTIONALLY] ENCLOSED BY 'char']        [ESCAPED BY 'char']    ]    [LINES        [STARTING BY 'string']        [TERMINATED BY 'string']    ]    [IGNORE number {LINES | ROWS}]    [(col_name_or_user_var,...)][SETcol_name = expr,...]

例2 MySQLload示例。

CREATE TABLE jokes  (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  joke TEXT NOT NULL);LOAD DATA INFILE'/tmp/jokes.txt' INTO TABLE jokes  FIELDS TERMINATED BY ''  LINES TERMINATED BY '\n%%\n' (joke);

存储引擎

MySQL属于独有的插件式结构，支持多种存储引擎，每种引擎都有自己的特性。可根据实际需要选用不同的存储引擎。

其中常用的几个引擎如下：

MyISAM  不支持事务，无特殊原因不建议在生产环境使用。

InnoDB   属于当前主流存储引擎，被广泛采用，支持事务。

NDB 分布式数据库

DB2 属于单一存储引擎。

MySQL可以在创建表时指定engine选项，例如：

CREATE TABLE `customer` (  `C_CUSTKEY` int(11) NOT NULL,  `C_NAME` varchar(25) NOT NULL,  `C_ADDRESS` varchar(40) NOT NULL,  `C_NATIONKEY` int(11) NOT NULL,  `C_PHONE` char(15) NOT NULL,  `C_ACCTBAL` decimal(15,2) NOT NULL,  `C_MKTSEGMENT` char(10) NOT NULL,  `C_COMMENT` varchar(117) NOT NULL,  PRIMARY KEY (`C_CUSTKEY`),) ENGINE=InnoDB DEFAULTCHARSET=utf8

也可以使用alter table语句变更表引擎：

Alter table table_name ENGINE[=] engine_name;

数据类型

数据类型方面MySQL主要有以下几方面和DB2不同：

MySQL单行最长65532字节。

MySQL date类型不支持默认值。

MySQL使用ENUM作为check约束。

MySQL的BLOB最大为4G，不支持默认值。

DDL

DDL方面MySQL主要有以下几方面和DB2不同：

Mysql仅支持自增ID，且自增列必须为主键。

MySQL表分区支持range, list, hash, key分区方式。

MySQL不支持date默认值为当前。

MySQL无法指定独立的索引表空间。

例1 在MySQL中创建一个含有自增列的表。

CREATE TABLE t1 (  c1 INT(11) NOT NULL AUTO_INCREMENT,  c2 VARCHAR(10) DEFAULT NULL,  PRIMARY KEY (c1)) ENGINE=InnoDB;

例2 在MySQL中创建一个分区表。

CREATE TABLE t1 (    id INT,    year_col INT)PARTITION BY RANGE(year_col) (    PARTITION p0 VALUES LESS THAN (1991),    PARTITION p1 VALUES LESS THAN (1995),    PARTITION p2 VALUES LESS THAN (1999),    PARTITION p3 VALUES LESS THAN (2003),    PARTITION p4 VALUES LESS THAN (2007));

Schema

DB2中的schema是依赖于database存在的，database下面有schema，schema下面有表。

MySQL中的database等同于schema，schema下面直接有表，进入一个schema时执行“use ‘schema_name’”。

例1 在MySQL中创建一个数据库mytest。

mysql> create databasemytest;Query OK, 1 row affected(0.01 sec)mysql>

例2 在MySQL中进入mytest数据库。

mysql> use mytestDatabase changedmysql>

DML

DML方面MySQL主要有以下几方面和DB2不同：

MySQL不支持单行附带隔离级别的update。

MySQL使用limit语法代替fetch first语法。

MySQL的limit m,n语法支持翻页。

MySQL不支持窗口函数。

例1 在MySQL查询中使用limit语句。

mysql> SELECT * FROMratings ORDER BY category LIMIT 5;+----+----------+--------+| id | category | rating |+----+----------+--------+|  1 |       1 |    4.5 ||  5 |       1 |    3.2 ||  4 |       2 |    3.5 ||  3 |       2 |    3.7 ||  6 |       2 |    3.5 |+----+----------+--------+

存储过程

存储过程（StoredProcedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

DB2支持存储过程。

MySQL的存储过程只支持基本特性。

例1 在MySQL中创建并调用procedure。

mysql> delimiter mysql> CREATE PROCEDUREsimpleproc (OUT param1 INT)    -> BEGIN    ->  SELECT COUNT(*) INTO param1 FROM t;    -> END//Query OK, 0 rows affected(0.00 sec)mysql> delimiter ;mysql> CALLsimpleproc(@a);Query OK, 0 rows affected(0.00 sec)mysql> SELECT @a;+------+| @a   |+------+| 3    |+------+1 row in set (0.00 sec)

触发器

触发器是一种特殊的存储过程，它在试图更改触发器所保护的数据时自动执行。

触发器方面MySQL主要有以下几方面和DB2不同：

MySQL支持多个event直接设置为单个触发器。

在MySQL5.7之前的版本，单表最多只能有一个触发器。

MySQL创建trigger的语法：

CREATE    [DEFINER = { user | CURRENT_USER }]    TRIGGER trigger_name    trigger_time trigger_event    ON tbl_name FOR EACH ROW    [trigger_order]    trigger_bodytrigger_time: { BEFORE |AFTER }trigger_event: { INSERT |UPDATE | DELETE }trigger_order: { FOLLOWS |PRECEDES } other_trigger_name

例1 在MySQL中创建一个trigger。

mysql> CREATE TABLEaccount (acct_num INT, amount DECIMAL(10,2));Query OK, 0 rows affected(0.03 sec)mysql> CREATE TRIGGERins_sum BEFORE INSERT ON account       FOR EACH ROW SET @sum = @sum +NEW.amount;Query OK, 0 rows affected(0.01 sec)

安全认证

安全认证方面MySQL主要有以下几方面和DB2不同：

MySQL将user@host作为一个独立用户。

MySQL支持最细粒度为列级别的授权。

MySQL无法对行级别进行授权。

MySQL商业版支持外部方式认证。

MySQL创建用户语法：

CREATE USER [IF NOT EXISTS]    user [auth_option] [, user [auth_option]]...    [REQUIRE {NONE | tls_option [[AND]tls_option] ...}]    [WITH resource_option [resource_option]...]    [password_option | lock_option] ...user:    (see Section 7.2.3, “Specifying AccountNames”)auth_option: {    IDENTIFIED BY 'auth_string'  | IDENTIFIED BY PASSWORD 'hash_string'  | IDENTIFIED WITH auth_plugin  | IDENTIFIED WITH auth_plugin BY'auth_string'  | IDENTIFIED WITH auth_plugin AS'hash_string'}

tls_option: {   SSL | X509 | CIPHER 'cipher' | ISSUER 'issuer' | SUBJECT 'subject'}resource_option: {    MAX_QUERIES_PER_HOUR count  | MAX_UPDATES_PER_HOUR count  | MAX_CONNECTIONS_PER_HOUR count  | MAX_USER_CONNECTIONS count}password_option: {    PASSWORD EXPIRE  | PASSWORD EXPIRE DEFAULT  | PASSWORD EXPIRE NEVER  | PASSWORD EXPIRE INTERVAL N DAY}lock_option: {    ACCOUNT LOCK  | ACCOUNT UNLOCK}

例1 在MySQL中创建一个只能从本地登陆的用户。

CREATE USER'jeffrey'@'localhost'  IDENTIFIED WITH sha256_password BY'new_password'  PASSWORD EXPIRE INTERVAL 180 DAY;