MySQL 8.0 Reference Manual（读书笔记20节– NULL+模式匹配+外键+自增属性）

1.NUll值

The NULL value can be surprising until you get used to it. Conceptually【kənˈsɛptʃuəli 概念;观念上;概念上;在概念上;概念地;】, NULL means “a missing unknown value” and it is treated somewhat differently from other values.

To test for NULL, use the IS NULL and IS NOT NULL operators, as shown here:

mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
| 0         | 1             |
+-----------+---------------+

 You cannot use arithmetic comparison【kəmˈpærɪsn 比较;对比;相比;】 operators such as =, <, or <> to test for NULL. To demonstrate this for yourself, try the following query:

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
| NULL     | NULL      | NULL     | NULL     |
+----------+-----------+----------+----------+

Because the result of any arithmetic comparison with NULL is also NULL, you cannot obtain any meaningful results from such comparisons.

In MySQL, 0 or NULL means false and anything else means true. The default truth value from a boolean operation is 1.

Two NULL values are regarded as equal in a GROUP BY.

When doing an ORDER BY, NULL values are presented first if you do ORDER BY … ASC and last if you do ORDER BY … DESC.

A common error when working with NULL is to assume that it is not possible to insert a zero or an empty string into a column defined as NOT NULL, but this is not the case. These are in fact values, whereas NULL means “not having a value.” You can test this easily enough by using IS [NOT] NULL as shown:

mysql> SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
+-----------+---------------+------------+----------------+
| 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL |
+-----------+---------------+------------+----------------+
| 0         | 1             | 0          | 1              |
+-----------+---------------+------------+----------------+

Thus it is entirely【ɪnˈtaɪərli 完全;完整地;全部地;】 possible to insert a zero or empty string into a NOT NULL column, as these are in fact NOT NULL.

 2.模式匹配

MySQL provides standard SQL pattern matching as well as a form of pattern matching based on extended regular expressions similar to those used by Unix utilities such as vi, grep, and sed.

2.1 通过 _ 和  %

SQL pattern matching enables you to use _ to match any single character and % to match an arbitrary number of characters (including zero characters). In MySQL, SQL patterns are case-insensitive by default. Some examples are shown here. Do not use = or <> when you use SQL patterns. Use the LIKE or NOT LIKE comparison operators instead.

2.2 通过extended regular expressions

The other type of pattern matching provided by MySQL uses extended regular expressions. When you test for a match for this type of pattern, use the REGEXP_LIKE() function (or the REGEXP or RLIKE operators, which are synonyms for REGEXP_LIKE()).

The following list describes some characteristics of extended regular expressions:

• . matches any single character

• A character class […] matches any character within the brackets【ˈbrækɪts (固定在墙上的)托架，支架;括号;(价格、年龄、收入等的)组级，等级;】. For example, [abc] matches a, b, or c. To name a range of characters, use a dash. [a-z] matches any letter, whereas [0-9] matches any digit.

• * matches zero or more instances of the thing preceding it. For example, x* matches any number of x characters, [0-9]* matches any number of digits, and .* matches any number of anything.

• A regular expression pattern match succeeds if the pattern matches anywhere in the value being tested. (This differs from a LIKE pattern match, which succeeds only if the pattern matches the entire value.)

• To anchor a pattern so that it must match the beginning or end of the value being tested, use ^ at the beginning or $ at the end of the pattern.

 To demonstrate how extended regular expressions work, the LIKE queries shown previously are rewritten here to use REGEXP_LIKE().

To find names beginning with b, use ^ to match the beginning of the name: –开头

mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b');
+--------+--------+---------+------+------------+------------+
| name | owner    | species | sex  | birth      | death |
+--------+--------+---------+------+------------+------------+
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Bowser | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+

 To force a regular expression comparison to be case-sensitive, use a case-sensitive collation, or use the BINARY keyword to make one of the strings a binary string, or specify the c match-control character. Each of these queries matches only lowercase b at the beginning of a name:

SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b' COLLATE utf8mb4_0900_as_cs);
SELECT * FROM pet WHERE REGEXP_LIKE(name, BINARY '^b');
SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b', 'c');

To find names ending with fy, use $ to match the end of the name: —结尾

mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, 'fy$');
+--------+--------+---------+------+------------+-------+
| name   | owner  | species | sex  | birth      | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL |
| Buffy | Harold  | dog     | f    | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+

To find names containing a w, use this query: —不限位置的包含

mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, 'w');
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+------------+
| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |
| Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
+----------+-------+---------+------+------------+------------+

Because a regular expression pattern matches if it occurs anywhere in the value, it is not necessary in the previous query to put a wildcard【ˈwaɪldˌkɑrd (用于代替任何字符或字符串的)通配符;(给予没有正常参赛资格的选手准其参加比赛的)“外卡”;“外卡”选手;未知数;未知因素;】 on either side of the pattern to get it to match the entire value as would be true with an SQL pattern.

To find names containing exactly five characters, use ^ and $ to match the beginning and end of the name, and five instances of . in between:

mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.....$');
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

You could also write the previous query using the {n} (“repeat-n-times”) operator:

mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.{5}$');
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+

 3.Using Foreign Keys

MySQL supports foreign keys, which permit cross-referencing related data across tables, and foreign key constraints, which help keep the related data consistent.

A foreign key relationship involves【ɪnˈvɑːlvz 需要;影响;(使)参加，加入;包含;牵涉;牵连;使成为必然部分(或结果);】 a parent table that holds the initial【ɪˈnɪʃl 开始的;最初的;第一的;】 column values, and a child table with column values that reference the parent column values. A foreign key constraint is defined on the child table.

This following example relates parent and child tables through a single-column foreign key and shows how a foreign key constraint enforces referential integrity.

Create the parent and child tables:

CREATE TABLE parent (
 id INT NOT NULL,
 PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (
 id INT,
 parent_id INT,
 INDEX par_ind (parent_id),
 FOREIGN KEY (parent_id)
 REFERENCES parent(id)
) ENGINE=INNODB;

Insert a row into the child table with a parent_id value that is not present in the parent table:–报错

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails 
(`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) 
REFERENCES `parent` (`id`))

The operation fails because the specified parent_id value does not exist in the parent table.

Try to delete the previously inserted row from the parent table:–删除父表中的数据，但是这笔数据，子表有参照，就会报错

mysql> DELETE FROM parent WHERE id VALUES = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails 
(`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) 
REFERENCES `parent` (`id`))

This operation fails because the record in the child table contains the referenced id (parent_id) value.

When an operation affects a key value in the parent table that has matching rows in the child table, the result depends on the referential action specified by ON UPDATE and ON DELETE subclauses【’sʌbklɔ:z 法律文件的）下设条款】 of the FOREIGN KEY clause. Omitting【əˈmɪtɪŋ 忽略;遗漏;删除;漏掉;不做;未能做;】 ON DELETE and ON UPDATE clauses (as in the current child table definition) is the same as specifying the RESTRICT option, which rejects【rɪˈdʒekts 拒收;拒绝接受;拒绝接纳;(因质量差)不用，不出售，不出版;不予考虑;不录用;】 operations that affect a key value in the parent table that has matching rows in the parent table.

To demonstrate ON DELETE and ON UPDATE referential actions, drop the child table and recreate it to include ON UPDATE and ON DELETE subclauses with the CASCADE option. The CASCADE option automatically deletes or updates matching rows in the child table when deleting or updating rows in the parent table.

DROP TABLE child;
CREATE TABLE child (
 id INT,
 parent_id INT,
 INDEX par_ind (parent_id),
 FOREIGN KEY (parent_id)
 REFERENCES parent(id)
 ON UPDATE CASCADE
 ON DELETE CASCADE
) ENGINE=INNODB;

ON UPDATE CASCADE referential action updated the child table。–父变子已变

ON DELTE CASCADE referential action, delete records from the parent table，the ON DELETE CASCADE referential action removes all records from the child table。–删除父表中的数据，子表中的数据，同时删除。

 4.Using AUTO_INCREMENT

The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows。

No value was specified for the AUTO_INCREMENT column, so MySQL assigned sequence numbers automatically.

If the column is declared NOT NULL, it is also possible to assign NULL to the column to generate sequence numbers.—你在insert语句中，虽然values值指明了null，自动生产AUTO INCREMENT的序列值

When you insert any other value into an AUTO_INCREMENT column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the largest column value.–如果自增列指明的插入值，以插入值为准。并且会重置基数值。

Updating an existing AUTO_INCREMENT column value also resets the AUTO_INCREMENT sequence.–更新语句也会重置基数值。

You can retrieve the most recent automatically generated AUTO_INCREMENT value with the LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function. These functions are connection-specific, so their return values are not affected by another connection which is also performing inserts.

Use the smallest integer data type for the AUTO_INCREMENT column that is large enough to hold the maximum sequence value you require. When the column reaches the upper limit of the data type, the next attempt to generate a sequence number fails. Use the UNSIGNED attribute if possible to allow a greater range. For example, if you use TINYINT, the maximum permissible sequence number is 127. For TINYINT UNSIGNED, the maximum is 255.–小心取值范围

说明

For a multiple-row insert, LAST_INSERT_ID() and mysql_insert_id() actually return the AUTO_INCREMENT key from the first of the inserted rows. This enables multiple-row inserts to be reproduced correctly on other servers in a replication setup.

—-官网第三章《Tutorial 》