数据库设计的细节说明

索引

索引是一种数据库用于提高SELECT查询效率的特殊体制。虽然表的最大索引数可能会有各种限制，但MySQL至少能保证为每个表创建16个索引，每个索引可以最多包含15个列。

虽然索引提高了数据库的读取速度，但是减慢了对更改数据的查询（因为这些更改数据需要记录在索引中）。

适用范围

1、在查询的WHERE部分中频繁使用的列

2、在查询的ORDER BY部分中频繁使用的列

3、经常被用于JOIN连结点的列

非适用范围

1、允许为空的列

2、字段值的范围很有限的列

索引类型（4种）

1、INDEX：标准索引

2、UNIQUE：每一行对索引列具有唯一的值

3、FULLTEXT：用于执行FULLTEXT查找

4、PRIMARY KEY：一个特殊的UNIQUE索引，主键

语法

INDEX_TYPE index_name (columns)

补充

多列索引是多个列同时使用一个索引

MySQL使用KEY作为索引的同义词

表类型（存储引擎）

MyISAM

优点：处理SELECT和INSERT非常快。

缺点：不能处理事务。缺少行级的锁定，容易损坏并且在发生崩溃时数据容易丢失。

InnoDB（MySQL5.5.5版本之后默认的类型）

优点：可以使用事务，在处理UPDATE时表现良好。支持外键约束和行级锁定。

缺点：占用磁盘空间较大，不支持FULLTEXT索引。MySQL5.5.5版本之后默认的类型。

语法

CREATE TABLE tablename ( column1name COLUMNTYPE, column2name COLUMNTYPE;

SHOW ENGINES; //查看MySQL上的引擎

字符集

数据库使用的字符集必须和服务器端的脚本语言的字符集一样。

校对规则名字的后缀ci表示不区分大小写；cs表示区分大小写；bin表示二元校对规则；

语法

创建数据库时定义字符集和校对规则的语法

CREATE DATABASE name CHARACTER SET charset COLLATE collation;

修改通信编码的语法

CHARSET utf8;

创建表时定义字符集和校对规则的语法

CREATE TABLE name (

column definitions

) CHARACTER SET charset COLLATE collation;

创建列时定义字符集和校对规则的语法

CREATE TABLE name (

something TEXT CHARACTER SET charset COLLATE collation 

…)

时区函数

UTC_DATE()：返回UTC日期

UTC_TIME()：返回当前的UTC时间

UTC_TIMESTAMP()：返回当前的日期和时间

CONVERT_TZ(dt, from, to)：将一个时间或日期从一个时区调整到另一个

外键约束

InnoDB类型表有一个其他存储引擎不支持的特性——应用外键的能力

外键约束设定了当约束被破坏发生时应该应用的规则，包括防止约束放生。

语法

创建外键约束的语法（只标识了关系）

FOREIGN KEY (item_name) REFERENCES table (column)

注：item_name是外键列、table(column)子句是约束该外键的父表列的引用

触发动作（5种）

1、RESTRICT：默认，不指定任何动作

2、NO ACTION：默认，不指定任何动作

3、SET DEFAULT

4、CASCADE：最有用的操作，如果删除父记录，也将删除那个以父ID作为外键的记录。由于只有InnoDB支持外键约束，所以两个表的类型都应该为InnoDB

5、SET NULL：删除父记录将导致子表中相应的外键被设置为NULL。如果表的该列被设置为NOT NULL，删除父记录将引发一个错误。

语法

ON DELETE action;

ON UPDATE action;

举例

CREATE TABLE accounts (

account_id INT UNSIGNED NOT NULL AUTO_INCREMENT, 18 customer_id INT UNSIGNED NOT NULL,

type ENUM('Checking', 'Savings') NOT NULL,

balance DECIMAL(10,2) UNSIGNED NOT NULL DEFAULT 0.0,

PRIMARY KEY (account_id),

INDEX (customer_id),

FOREIGN KEY (customer_id) REFERENCES customers (customer_id) ON DELETE NO ACTION ON UPDATE NO ACTION

) ENGINE = INNODB;