《MySQL技术内幕》Note

发表于 | 分类于 | | 阅读次数
  • 第一本读完的mysql书
  • 时间

阅读前准备

书中的示例一共用到三个数据库文件：一个是 employees 数据库，该数据库是 MySQL 数据库官方提供的示例数据库，主要用来模拟公司员工的数据，用户可以通过官网下载 (http://dev.mysql.com/doc/index-other.html）；另一个是 dbt3 数据库，是通过 Database Test Suite 程序生成的，该数据库较大，主要用来展示一些复杂的查询；还有一个 tpcc 数据库，是一个模拟 TPC-C 测试的数据库，用户可以从http://code.google.com/p/david-mysql-tools/下载

字节B(Byte), 1B=8bit(位)
字符与字节：

  • ASCII码： 一个英文字母（不分大小写）占一个字节的空间，一个中文汉字占两个字节的空间
  • UTF-8编码：一个英文字符等于一个字节，一个中文（含繁体）等于三个字节。中文标点占三个字节，英文标点占一个字节
  • Unicode编码：一个英文等于两个字节，一个中文（含繁体）等于两个字节。中文标点占两个字节，英文标点占两个字节

国际单位制(SI)
1KB=1000B；1MB=1000KB=1000×1000B。其中1000=103。
1B（byte，字节）= 8 bit

一、SQL编程

二、数据类型

2.1 类型属性

2.1.1 UNSIGNED

1
2
3
4
5
6
7
create database book_mysql default character set utf8;
use book_mysql;
create table  t ( a int unsigned, b int unsigned) engine=InnoDB;
insert into t select 1,2;
select * from t\G;
select a-b from t;
-- mac下报错：ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`book_mysql`.`t`.`a` - `book_mysql`.`t`.`b`)'

在 MySQL 数据库中，对于 UNSIGNED 数的操作，其返回值都是 UNSIGNED 的, C语言内部会将其转为最大值，即0xFFFFFFFF (4294967295)。这样需要在mysql中设置

SET sql_mode=’NO_UNSIGNED_SUBTRACTION’;

这时候就返回了-1

2.1.2 ZEROFILL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> show create table t\G;
***************************[ 1. row ]***************************
Table        | t
Create Table | CREATE TABLE `t` (
  `a` int(10) unsigned DEFAULT NULL,
  `b` int(10) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set
Time: 0.012s
mysql> select * from t\G;
mysql> alter table t change column a a int(4) unsigned zerofill;
mysql> select * from t\G;
*************************** 1. row ***************************
a: 0001
b: 2
1 row in set (0.00 sec)
mysql> select a,hex(a) from t\G;
*************************** 1. row ***************************
     a: 0001
hex(a): 1
1 row in set (0.01 sec)

zerofill字段，在数据内部存储的还是原来的数字，前面的0是zerofill属性后的一种格式输出而已

2.2 SQL_MODE设置

1
2
3
4
5
6
> mysql: select @@global.sql_mode\G; -- 全局sql_mode设置
@@global.sql_mode: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
> select @@session.sql_mode\G; -- 当前会话sql_mode设置
@@session.sql_mode: NO_UNSIGNED_SUBTRACTION
1 row in set (0.00 sec)

  • STRICT_TRANS_TABLES：在该模式下，如果一个值不能插人到一个事务表（例如表的存储引擎为 InnoDB）中，则中断当前的操作不影响非事务表（例如表的存储引擎为 MyISAM）。

  • ALLOW_INVALID_DATES：该选项并不完全对日期的合法性进行检查，只检查月份是否在 1~12 之间，日期是否在 1 ~31 之间。该模式仅对 DATE 和 DATETIME 类型有效，而对 TIMESTAMP 无效，因为 TIMESTAMP 总是要求一个合法的输人。

  • ANSI_QUOTES：启用 ANSI_QUOTES 后，不能用双引号来引用字符串，因为它将被解释为识别符

  • ERROR_FOR_ DIVISION_BY_ZERO：在 INSERT 或 UPDATE 过程中，如果数据被零除（或 MOD (X, 0）），则产生错误（否则为警告）。如果未给出该模式，那么数据被零除时 MySQL 返回 NULL。如果用到 INSERT IGNORE 或 UPDATE IGNORE 中，MySQL 生成被零除警告，但操作结果为 NULL。

  • HIGH_NOT_PRECEDENCE NOT：操作符的优先顺序是表达式。例如，NOT a BETWEEN b AND c 被解释为 NOT (a BETWEEN b AND c），在一些旧版本 MySQL 中，前面的表达式被解释为（NOT a) BETWEEN b AND C。启用 HIGH NOT PRECEDENCE SQL 模式，可以获得以前旧版本的更高优先级的结果。

  • IGNORE_SPACE：函数名和括号“（”之间有空格。除了增加一些烦恼，这个选项好像没有任何好处，要访问保存为关键字的数据库、表或列名，用户必须引用该选项。例如某个表中有 user 这一列，而 MySQL 数据库中又有 user 这个函数，user 会被解释为函数，如果想要选择 user 这一列，则需要引用。

  • NO_AUTO_CREATE_USER：禁止 GRANT 创建密码为空的用户。

  • NO_AUTO_VALUE_ON_ZERO：该选项影响列为自增长的插入。在默认设置下，插入 0 或 NULL 代表生成下一个自增长值。如果用户希望插入的值为 0，而该列又是自增长的，那么这个选项就有用了。

  • NO_BACKSLASH_ESCAPES：反斜杠“\”作为普通字符而非转义符，示例如下：

    1
    2
    3
    4
    set sql_mode='';
    select '\\'\G;
    set sql_mode='NO_BACKSLASH_ESCAPES';
    select '\\'\G;

  • NO_DIR_IN_CREATE：在创建表时忽视所有 INDEX DIRECTORY 和 DATADIRECTORY 的选项。

  • NO_ENGINE_SUBSTITUTION：如果需要的存储引擎被禁用或未编译，那么抛出错误。默认用默认的存储引擎替代，并抛出一个异常。
  • NO_UNSIGNED_SUBTRACTION：之前已经介绍过，启翩这个选项后两个 UNSIGNED 类型相减返回 SIGNED 类型。
  • NO_ZERO_DATE：在非严格模式下，可以插入形如“0000-00-00 00:00:00”的非法日期，MySQL 数据库仅抛出一个警告。而启用该选项后，MySQL 数据库不允许插人零日期，插人零日期会抛出错误而非警告。
  • NO_ZERO_IN_DATE：在严格模式下，不允许日期和月份为零。如“2011-00-01”和“2011-01-00”这样的格式是不允许的。采用日期或月份为零的格式时 MySQL 都会直接抛出错误而非警告，示例如下：
1
2
3
4
5
6
7
8
9
10
11
mysql> SET sql_ mode= 'NO_ ZERO_ IN_ DATE';
Query OK, 0 rows affected  (0.00 sec)
mysql> CREATE TABLE a  (a DATETIME);
Query OK，0 rows af fected  (0.04 sec)
mysql> INSERT INTO a SELECT '2011-01-00';
ERROR 1292  (22007): Incorrect datet ime value: '2011-01-00' for column 'a' at row 1

  • ONLY_FULL_GROUP_BY：对于 GROUPBY 聚合操作，如果在 SELECT 中的列没有在 GROUP BY 中出现，那么这句 SQL 是不合法的，因为 a 列不在 GROUPBY 从句中，示例如下：

    1
    2
    SET sql_mode= 'ONLY_FULL_GROUP_BY';
    SELECT a, SUM (b) FROM t GROUP BY b;

  • PAD_CHAR__TO_FULL_LENGTH：对于 CHAR 类型字段，不要截断空洞数据。空洞数据就是自动填充值为 0 x20 的数据。先来看 MySQL 数据库在默认情况下的表现。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    mysql> CREATE TABLE  (a CHAR (10));
    Query OK，0 rows affected  (0.04 sec)
    mysql> INSERT INTO t SELECT 'a';
    Query OK，1 row affected  (0.01 sec) Records; 1 Duplicates: 0 Warnings: 0
    mysql> SELECT a, CHAR LENGTH (a), HEX (a) FROM t\G;
    ★************************** 1. Row ***********************★★★★
    a: a CHAR LENGTH (a): 1
        HEX (a): 61 
    1 row in set  (0.04 sec)

可以看到，在默认情况下，虽然 a 列是 CHAR 类型，但是返回的长度是 1, 这是因为 MySQL 数据库已经对后面的空洞数据进行了截断。若启用 PAD CHAR TO FULL LENGTH 选项，则反映的是实际存储的内容，例如：

1
2
3
4
5
6
mysql> SELECT a, CHAR LENGTH (a), HEX (a) FROM t\G;
***************************1. Row *★★**★*****★*********★**★★*
a: a
CHAR LENGTH (a): 10
HEX (a): 61202020202020202020
1 row in set  (0.00 sec)

可以看到在 CHAR 列 a 中实际存储的值为 0 x61202020202020202020。

  • PIPES_AS_CONCAT：将“”视为字符串的连接操作符而非或运算符，这和 Oracle 数据库是一样的，也和字符串的拼接函数 Concat 相类似，例如：

    1
    2
    SET sq1_mode= 'pipes_as_concat';
    SelECT 'a'||'b'||'c'\G;

  • REAL_AS_FLOAT：将 REAL 视为 FLOAT 的同义词，而不是 DOUBLE 的同义词。

  • STRICT_ALL_TABLES：对所有引擎的表都启用严格模式。（STRICT_TRANS_TABLES 只对支持事务的表启用严格模式）。

在严格模式下，一旦任何操作的数据产生问题，都会终止当前的操作。对于启用 STRICT_ALL_TABLES 选项的非事务引擎来说，这时数据可能停留在一个未知的状态。这可能不是所有非事务引擎愿意看到的一种情况，因此需要非常小心这个选项可能带来的潜在影响。

2.3 日期和时间类型

TODO：暂不阅读