MySQL服务器的SQL模式

影响的行为包括在数据录入阶段如何处理非法数据,如何引用各种标识符等。

查询一个数据库的会话级或者全局级的SQL模式的当前值:

1
2
select @@SESSION.sql_mode;
select @@global.sql_mode;

在我本机上输出是(两者一致):

1
2
3
| @@SESSION.sql_mode
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
1 row in set (0.00 sec)

SQL语句中的字母大小问题

  • SQL关键字和函数名不区分字母的大小写。
  • 数据库,数据表和视图的名字在Windows和MAC中不区分大小写,但是在Unix系统是区分大小写的。
  • 存储函数,存储过程和事件的名字不区分字母大小写。触发器的名字区分大小写。
  • 数据列和索引的名字不区分大小写。

统一使用小写字母,这样InnoDB数据表也有好处,其引擎内部是把数据库和数据表的名字作为小写字母存储的。

而且查询的时候最好使用一致的大小写组合来引用它。这样具有更高的可读性。

数据库选定,创建,删除和变更

  • USE db_name;
  • CREATE DATABASE db_name

    创建数据库的时候,MySQL服务器会在数据目录里创建一个与该数据库同名的子目录,这个新目录称为数据库子目录。服务器还会在数据库目录里创建一个db.opt文件来保存数据库的属性,比如字符集和排序方式等属性。

    CREATE DATABASE [IF NOT EXISTS] db_name [CHARACTER SET charset] [COLLATE collation]

    后面一个是设置数据库的默认字符集,一个是排序方式。比如CREATE DATABASE mydb CHARACTER SET utf8 COLLATE utf8_icelandic_ci;

  • SHOW CREATE DATABASE mydb\G 查看现有的数据库的定义。\G每列打印单独的行,后面不需要再加分号。

  • DROP DATABASE db_name;

    一个数据库就是MySQL数据目录里的一个子目录,这个子目录用于存放数据表视图和触发器等。如果该语句失效,则需要手动删除该数据库子目录里的遗留文件和子目录本身,然后再发出该语句。

  • ALTER DATABASE [db_name] [CHARACTER SET charset] [COLLATE collation];

    改变数据库的全局特性,也就是默认字符集和排序规则。这里SCHEMA是DATABASE的同义词。

数据表的创建,删除,索引和变更

MySQL为管理不同类型的数据表而支持几种不同的存储引擎(数据表处理器)。由同一个存储引擎所实现的数据表具有一些共同的属性或者特征。

引擎 说明
ARCHIVE 数据存档,不能修改
CSV 存储CSV
InnoDB 具备外键支持功能的事务处理引擎
MyISAM 默认的存储引擎
MEMORY 内存里的数据表
  • SHOW ENGINES查出服务器都知道哪些存储引擎。

    Transactions表明存储引擎是否支持事务,XA表明存储引擎是否支持分布式事务处理,Savepoints是否支持部分事务回滚。

    另外一种方式是SELECT ENGINE FROM INFORMATION_SCHEMA.ENGINES;也可以查出所有的存储引擎。

每创建一个数据表,MySQL就会创建一个硬盘文件来保存该数据表的格式(定义),.frm,存储在相应database名的目录下。然后不同的存储引擎会为特定数据表创建几个特定的文件。

引擎 文件
MyISAM .MYD(数据), .MYI(索引)
InnoDB .ibd(数据和索引)
CSV .CSV(数据), .CSM(元数据)
ARCHIVE .ARZ(数据), .ARM(元数据)

MyISAM存储引擎

  • 提供了索引压缩功能

    MyISAM使用前缀压缩来减少索引的大小,从而让更多的索引可以放入内存中,默认只压缩字符串,但通过参数配置也可以对整数做压缩,myisam压缩每个索引块的方法是,先完全保存索引块中的第一个值,然后将其他值和第一个值进行比较得到相同前缀的字节数(长度)和剩余的不同后缀部分(即把相同部分去掉),把这部分存储起来即可(相同前缀长度和不同后缀部分字符串)。如:索引块中的第一个值是perform,第二个是performance,那么第二个值的前缀压缩后存储的是类似7,ance,这样的形式,myisam对行指针也采用类似的前缀压缩方式。

      压缩块使用更少的空间,代价是某些操作可能更慢,因为每个值的压缩前缀都依赖于前面的值,所以myisam查找时无法在索引块使用二分查找而只能从头开始扫描,正序的扫描速度还不错,但如果是倒序扫描,如:order by desc就不是很好了,所有在块中查找某一行的操作平均都需要扫描半个索引块。

    测试表明,对于CPU密集型应用,因为扫描需要随机查找,压缩索引使得myisam在索引查找上要慢好几倍,压缩索引的倒序扫描就更慢了,压缩索引需要在CPU内存资源与磁盘之间做权衡,压缩索引可能只需要十分之一大小的磁盘,如果是IO密集型应用,对于某些查询带来的好处会比成本多很多。即压缩索引适合IO密集型应用,不太适合CPU密集型应用。

    可以在create table语句中指定pack_keys参数来控制myisam表的索引压缩方式:

    PACK_KEYS [=] {0 | 1 | DEFAULT}

    0:  表示关闭索引压缩

    1:  表示开启数字和字符串压缩

    default:表示只压缩超长字符串char和varchar列

    如果建表时不指定这个选项,那么默认只压缩字符串,不压缩数字

  • AUTO_INCREMENT数据列提供了更多的功能

  • 有一个标志来说明数据表是否需要进行修复
  • 支持全文检索,需要通过FULLTEXT索引实现
  • 支持空间数据类型和SPATIAL索引

MEMORY存储引擎

  • 散列索引,因此不太适合排序,范围比较
  • 数据行使用的长度固定不变的格式
  • 可以加入BTREE索引来加快范围比较的速度

InnoDB的存储引擎

  • 支持提交和回滚操作
  • 系统崩溃,自动恢复
  • 外键和引用完整性支持,递归式删除和更新
  • 数据行级别的锁定
  • 数据表集中存储在一个共享的表空间里,也可以为每一个数据表分别创建一个表空间。这时,数据表在它的数据库子目录里有一个对应的ibd文件。

存储引擎的可移植性

  • 使用mysqldump工具备份,然后在另一台服务器上载入备份
  • 二进制可移植性

MyISAM和InnoDB数据表存储格式和机器无关,具备二进制可移植性。但是有一个前提条件,被移植的数据表不含有任何浮点数据列,或者两台机器使用的浮点数存储格式是一样的。另外对于InnoDB数据库和数据表的名字应该是小写字母。

MAC中的mysql数据在: /usr/local/mysql/data/,下面每一个文件夹都是一个数据库,再往里就是每个数据库的数据表文件了,InnoDB就有frm和ibd文件

创建数据表

创建数据表需要给出数据表名字和数据列,其中包含各个数据列的定义,也可以指定如何创建索引。另一种方法是先创建一个不带索引的数据表,然后再加上索引,这种方式在查询之前填充大量数据的时候比较好,比每次插入数据都要刷新索引要好。

  1. 数据表选项

    CREATE TABLE mytbl (...) ENGINE=InnoDB;创建一个使用InnoDB引擎的数据表。

    SHOW CREATE TABLE t \G查询数据表使用的什么引擎。

    SHOW TABLE STATUS查询该数据库中所有数据表的状态。

    ALTER TABLE mytbl ENGINE=InnoDB;改变某一个数据表选项。

    • AUTO_INCREMENT=n为数据表设定一个起始编号值
    • [DEFAULT] CHARACTER SET=charset为数据表指定一个默认字符集,(CHARACTER SET==CHARSET),如果字符串数据列没有明确表示它使用哪个字符集,那么就会使用这个选项指定的字符集。
  2. CREATE TABLE IF NOT EXIST创建原本没有的数据表。DROP TABLE IF EXIST

  3. CREATE TEMPORARY TABLE tab_name将创建一个临时数据表,在与服务器断开时候自动消失。DROP TEMPORARY TABLE
  4. 从其他数据表或者查询结果创建数据表

    • CREATE TABLE ... LIKE会将原始表结构全部复制过来,包括索引,但是内容是空的。后面可以INSERT INTO new_tbl_name SELECT * FROM tbl_name
    • CREATE TABLE ... SELECT从任意一条select语句的查询结果创建新的数据表。但是不会复制数据列属性和索引。
  5. 使用MERGE数据表

    MERGE存储引擎把一组MyISAM表当成一个逻辑单元来对待,可以对它们同时进行查询。这组表必须有完全一样的结构。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    CREATE TABLE log_CCYY
    {
    dt DATETIME NOT NULL,
    info VARCHAR(100) NOT NULL,
    INDEX (dt)
    } ENGINE = MyISAM;
    CREATE TABLE log_merge
    {
    dt DATETIME NOT NULL,
    info VARCHAR(100) NOT NULL,
    INDEX (dt)
    } ENGINE = MERGE UNION = {log_2004, log_2005}

删除数据表

  • DROP TABLE tbl_name;
  • 也可以删除多个表:DROP TABLE tbl_name1, tbl_name2;
  • DROP TABLE IF EXISTS tbl_name;
  • DROP TEMPORARY TABLE tbl_name;

创建索引

索引是加快对数据表内容的访问速度的手段,特别是多个数据表的关联查询中。

  • 可以为单个数据列进行索引,也可以根据多个数据列构建复合索引
  • 索引可以包含独一无二的值,也可以包含重复的值
  • 可以为一个数据表建立多个索引
  • 可以为数据列的一个前缀创建索引,这样使得索引更小并且加快访问速度。

只有MyISAM支持FULLTEXT或者SAPTIAL索引。当然可以对数据表进行转换以支持相应的索引。ALTER TABLE tbl_name ENGINE=InnoDB;

  • 唯一索引,单列则数据不能重复,多列则组合不能重复
  • 普通索引,允许索引值重复
  • FULLTEXT索引,全文检索,MyISAM数据表
  • SPATIAL索引,MyISAM数据表和空间数据类型
  • HASH索引,MEMORY数据表的默认索引,可以改用BTREE索引代替这个索引。

ALTER TABLE tbl_name ADD INDEX index_name (index_columns);

其中index_name是可选的,是索引的名字。index_columns是想加索引的一个或者多个数据列。还有其他索引是UNIQUE, PRIMARY KEY, FULLTEXT, SPATIAL。另外,如果是PRIMARY KEYSPATIAL索引,必须是NOT NULL属性。

每个数据表只能有一个PRIMARY KEY,因为PRIMARY KEY的索引名字总是PRIMARY,而同一个数据表不能有两个同名索引。

PRIMARY KEY不能有NULL值,而UNIQUE可以,因为索引的多个NULL值被认为代表多个不同的东西。

可以使用CREATE INDEX添加索引:CREATE INDEX index_name ON tbl_name (index_columns);,这里index_name是必须给定的。

在CREATE表的时候可以为它创建索引,索引名字是可选的。可以在每一列定义后面进行,也可以在建表语句后面:

1
2
3
4
5
6
7
CREATE TABLE tbl_name(
i INT NOT NULL PRIMARY KEY,
j CHAR(10) NOT NULL UNIQUE,
... column definitions...
INDEX index_name (index_columns),
UNIQUE index_name (index_columns),
)

MEMORY表默认是HASH索引,可以加上USING BTREE来建立BTREE索引

1
2
3
4
5
6
CREATE TABLE tbl_name(
id INT NOT NULL,
name CHAR(100),
... column definitions...
INDEX USING BTREE (id)
) ENGINE = MEMORY;

使用前缀索引:

1
2
3
4
CREATE TABLE tbl_name(
name CHAR(30) NOT NULL,
INDEX (name(10))
);

删除索引

  • DROP INDEX index_name on tbl_name;,必须指定index_name
  • 删除PRIMARY KEY: index_name是PRIMARY,而且两边加上反引号。因为一个数据表只有一个PRIMARY KEY,而且名字总是PRIMARY
  • ALTER TABLE tbl_name DROP INDEX index_name;
  • ALTER TABLE tbl_name DROP PRIMARY KEY;
  • SHOW CREATE TABLE或者SHOW INDEX FROM tbl_name显示索引。

改变数据表的结构

ALTER TABLE tbl_name action...

  1. 改变数据列的数据类型

    • ALTER TABLE mytbl MODIFY i MEDIUMINT UNSIGNED;
    • ALTER TABLE mytbl CHANGE i i MEDIUMINT UNSIGNED;,CHANGE可以改变i数据列的名字,因此需要输入两遍,因此可以修改数据列的名字(保持原有定义不变)
    • ALTER TABLE t MODIFY c CHAR(20) CHARSET utf8;
  2. 数据表改用另一种存储引擎

    ALTER TABLE tbl_name ENGINE = engine_name

  3. 重新命名一个数据表

    • ALTER TABLE tbl_name RENAME TO new_tbl_name;
    • RENAME TABLE old_name TO new_name;
    • RENAME TABLE old_name1 TO new_name, old_name2 TO new_name2;
    • RENAME TABLE db.t TO test.t;

参考

  1. MySQL技术内幕 第四版 (Paul Dubois)
  2. myisam压缩(前缀压缩)索引