|
|
#19
京山游侠2006-03-06 00:42
在MySQL中，InnoDB 表提供对外键约束的检查。请查看Section 14.2, “The InnoDB Storage Engine”, 和 Section 1.9.5.5, “Foreign Keys”.
如果只是连接两个表,外键不是必须的。对于InnoDB以外的其它数据库引擎，也可以使用REFERENCES tbl_name(col_name) 子句来定义一个列，但是不产生任何效果， 只是当成一个备注来提示你这个字段和另外一个表中的字段有联系， 在使用这个子句的时候，认识到以下几条是非常重要的：
-
MySQL 不进行任何类型的 CHECK 以确认col_name 列一定存在于tbl_name表中 (甚至是tbl_name 表自己是否存在)。
-
当在一个外键所在的表中进行删除行之类的操作时，MySQL 不会在引用外键的表中进行任何动作， 换句话说，这个子句无论如何没有ON DELETE 或 ON UPDATE 动作的含义。 (即使你能在REFERENCES子句中写上ON DELETE 或 ON UPDATE 子句，它们也会被忽略。)
-
该语句创建一个列 ； 但是不 创建任何类型的索引或键值。
-
如果试图定义一个InnoDB 表，该语句将产生一个错误。 你可以创建一个和另外一个列有联系的列，如下： CREATE TABLE person (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE shirt (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
PRIMARY KEY (id)
);
INSERT INTO person VALUES (NULL, 'Antonio Paz');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);
INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);
SELECT * FROM person;
+----+---------------------+
| id | name |
+----+---------------------+
| 1 | Antonio Paz |
| 2 | Lilliana Angelovska |
+----+---------------------+
SELECT * FROM shirt;
+----+---------+--------+-------+
| id | style | color | owner |
+----+---------+--------+-------+
| 1 | polo | blue | 1 |
| 2 | dress | white | 1 |
| 3 | t-shirt | blue | 1 |
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
| 7 | t-shirt | white | 2 |
+----+---------+--------+-------+
SELECT s.* FROM person p, shirt s
WHERE p.name LIKE 'Lilliana%'
AND s.owner = p.id
AND s.color <> 'white';
+----+-------+--------+-------+
| id | style | color | owner |
+----+-------+--------+-------+
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
+----+-------+--------+-------+
在这种形式下，REFERENCES 并不在 SHOW CREATE TABLE 或者DESCRIBE语句的输出中显示： SHOW CREATE TABLE shirt\G
*************************** 1. row ***************************
Table: shirt
Create Table: CREATE TABLE `shirt` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`style` enum('t-shirt','polo','dress') NOT NULL,
`color` enum('red','blue','orange','white','black') NOT NULL,
`owner` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
在MyISAM 和 BerkeleyDB 表中，这种方式使用的REFERENCES被当成一个注释或 “暗示” 。
OR操作符对于使用一个键查询是最优化的， AND操作符也是如此。
一种特殊的情况是使用OR操作符来组合在两个键上的查询： SELECT field1_index, field2_index FROM test_table
WHERE field1_index = '1' OR field2_index = '1'
从MySQL 5.0.0版以后提供对这种情况的优化，请查看Section 7.2.6, “Index Merge Optimization”.
解决这个问题，还有一种有效率的办法，就是使用UNION 来联合两个 SELECT 语句的输出。请查看Section 13.2.7.2, “UNION Syntax”.
每一个SELECT 语句只从一个键查询，所有能够被优化： SELECT field1_index, field2_index
FROM test_table WHERE field1_index = '1'
UNION
SELECT field1_index, field2_index
FROM test_table WHERE field2_index = '1';
下面的例子演示了怎样通过一组以bit开头的函数来计算一个月中用户有多少天访问了Web页： CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
day INT(2) UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
(2000,2,23),(2000,2,23);
示例表中包含了 year-month-day 数据以指示用户访问该Web页的时间， 要知道每个月有多少天有用户访问该Web页，使用如下语句： SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
GROUP BY year,month;
返回结果如下： +------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 | 01 | 3 |
| 2000 | 02 | 2 |
+------+-------+------+
该查询计算每一个 year/month 组合中有多少不同的day， 重复的项将自动忽略。
AUTO_INCREMENT 属性可以用来自动为新增加的行产生唯一的标识。
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');
SELECT * FROM animals;
将返回： +----+---------+
| id | name |
+----+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+----+---------+
你可以通过LAST_INSERT_ID() SQL函数或者mysql_insert_id() C API 函数来获得最后一个 AUTO_INCREMENT 值。这些函数是独立于连接的，所以它们的返回值不会收到其它连接上插入的数据的影响。
注意： 对于多行的 insert语句，LAST_INSERT_ID() 和 mysql_insert_id() 实际上返回这一次插入的第一行的AUTO_INCREMENT 键的值， 这允许多行插入能够在其它的replication服务器上正确的重现。
对于MyISAM 和 BDB 表， 你可以指定多列索引中的第二列为AUTO_INCREMENT ，在这种情况下，AUTO_INCREMENT 列的值是通过 MAX(auto_increment_column) + 1 WHERE prefix=given-prefix计算的，当你想把数据放入有序群组的时候这是非常有用的。 CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (grp,id)
);
INSERT INTO animals (grp,name) VALUES
('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','lax'),('mammal','whale'),
('bird','ostrich');
SELECT * FROM animals ORDER BY grp,id;
将返回： +--------+----+---------+
| grp | id | name |
+--------+----+---------+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+--------+----+---------+
请注意在这种情况下 (当AUTO_INCREMENT 列为多列索引的第二列的时候)，当你删除了每一组中最大的 AUTO_INCREMENT 值之后，AUTO_INCREMENT 的值是可以重用的， 在 MyISAM 表中也是如此， 正常情况下AUTO_INCREMENT 值是不可重用的。
如果AUTO_INCREMENT 列是多列索引中的一列， MySQL 将使用该列来产生自动增量。 例如，如果animals 表中包含PRIMARY KEY (grp, id)和INDEX (id)，MySQL 将会忽略PRIMARY KEY 以产生自动增量序列，结果就是这个表中将只包含一个序列， 而不是每一个 grp 值都有一个单独的序列。
如果要让AUTO_INCREMENT 值不是从 1开始， 你可以使用 CREATE TABLE 或 ALTER TABLE来指定该值，例如： mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;
关于AUTO_INCREMENT 的更多信息：
|
