12.20.4 检测功能依赖性

以下讨论提供了 MySQL 检测功能依赖关系的方式的几个示例。这些示例使用以下 table 示法:

{X} -> {Y}

将其理解为“ * X 唯一地确定 Y ”,这也意味着 Y 在功能上取决于 X *。

这些示例使用world数据库,可以从https://dev.mysql.com/doc/index-other.html下载。您可以在同一页面上找到有关如何安装数据库的详细信息。

从键派生的功能依赖性

以下查询为每个国家/locale 选择口语计数:

SELECT co.Name, COUNT(*)
FROM countrylanguage cl, country co
WHERE cl.CountryCode = co.Code
GROUP BY co.Code;

co.Codeco的主键,因此co的所有列在功能上都依赖于它,如使用以下 table 示法 table 示:

{co.Code} -> {co.*}

因此,co.name在功能上取决于GROUP BY列,并且查询有效。

可以使用NOT NULL列上的UNIQUE索引代替主键,并且将应用相同的功能依赖性。 (对于允许NULL值的UNIQUE索引不是正确的,因为它允许多个NULL值,在这种情况下,唯一性会丢失.)

从多列键和相等性派生的功能依赖项

该查询为每个国家/locale 选择所有口语的列 table 以及有多少人说:

SELECT co.Name, cl.Language,
cl.Percentage * co.Population / 100.0 AS SpokenBy
FROM countryLanguage cl, country co
WHERE cl.CountryCode = co.Code
GROUP BY cl.CountryCode, cl.Language;

对(cl.CountryCodecl.Language)是cl的两列复合主键,因此列对唯一确定cl的所有列:

{cl.CountryCode, cl.Language} -> {cl.*}

此外,由于WHERE子句中的相等性:

{cl.CountryCode} -> {co.Code}

并且,因为co.Codeco的主键:

{co.Code} -> {co.*}

“唯一确定”关系是可传递的,因此:

{cl.CountryCode, cl.Language} -> {cl.*,co.*}

结果,该查询是有效的。

与前面的示例一样,可以使用NOT NULL列上的UNIQUE键代替主键。

可以使用INNER JOIN条件代替WHERE。相同的功能依赖性适用:

SELECT co.Name, cl.Language,
cl.Percentage * co.Population/100.0 AS SpokenBy
FROM countrylanguage cl INNER JOIN country co
ON cl.CountryCode = co.Code
GROUP BY cl.CountryCode, cl.Language;

功能依赖的特殊情况

WHERE条件或INNER JOIN条件中的相等性测试是对称的,而外部联接条件中的相等性测试则不是,因为 table 扮演不同的角色。

假设参照完整性被意外破坏,并且存在countrylanguage行,而country中没有对应的行。考虑与上一个示例相同的查询,但带有LEFT JOIN

SELECT co.Name, cl.Language,
cl.Percentage * co.Population/100.0 AS SpokenBy
FROM countrylanguage cl LEFT JOIN country co
ON cl.CountryCode = co.Code
GROUP BY cl.CountryCode, cl.Language;

对于给定的cl.CountryCode值,可以在匹配行中找到co.Code的值(由cl.CountryCode确定),或者在没有匹配项时由NULL补充(也由cl.CountryCode确定)。在每种情况下,此关系均适用:

{cl.CountryCode} -> {co.Code}

cl.CountryCode本身在功能上依赖于\ { cl.CountryCode , cl.Language }这是一个主键。

如果联接结果中的co.CodeNULL的补码,则co.Name也是如此。如果co.Code不是NULL的补码,则由于co.Code是主键,因此它确定co.Name。因此,在所有情况下:

{co.Code} -> {co.Name}

Which yields:

{cl.CountryCode, cl.Language} -> {cl.*,co.*}

结果,该查询是有效的。

但是,假设 table 被交换,如以下查询所示:

SELECT co.Name, cl.Language,
cl.Percentage * co.Population/100.0 AS SpokenBy
FROM country co LEFT JOIN countrylanguage cl
ON cl.CountryCode = co.Code
GROUP BY cl.CountryCode, cl.Language;

现在这种关系适用:

{cl.CountryCode, cl.Language} -> {cl.*,co.*}

实际上,为cl进行的所有NULL互补行都将放入一个组(它们的GROUP BY列均等于NULL),并且在该组内co.Name的值可以变化。该查询无效,MySQL 拒绝了该查询。

因此,外部联接中的功能依赖性与行列式列属于LEFT JOIN的左侧还是右侧有关。如果存在嵌套的外部联接或联接条件不完全由相等性比较组成,则对功能依赖性的确定将变得更加复杂。

功能依赖性和视图

假设对某个国家的看法产生了他们的代码,大写的名字以及它们有多少种不同的官方语言:

CREATE VIEW country2 AS
SELECT co.Code, UPPER(co.Name) AS UpperName,
COUNT(cl.Language) AS OfficialLanguages
FROM country AS co JOIN countrylanguage AS cl
ON cl.CountryCode = co.Code
WHERE cl.isOfficial = 'T'
GROUP BY co.Code;

该定义是有效的,因为:

{co.Code} -> {co.*}

在查看结果中,第一个选定的列是co.Code,它也是组列,因此确定所有其他选定的 table 达式:

{country2.Code} -> {country2.*}

MySQL 理解并使用此信息,如下所述。

该查询通过将视图与citytable 结合在一起来显示国家/locale,它们拥有多少种不同的官方语言以及拥有多少座城市:

SELECT co2.Code, co2.UpperName, co2.OfficialLanguages,
COUNT(*) AS Cities
FROM country2 AS co2 JOIN city ci
ON ci.CountryCode = co2.Code
GROUP BY co2.Code;

该查询是有效的,因为如前所述:

{co2.Code} -> {co2.*}

MySQL 能够发现视图结果中的功能依赖性,并使用它来验证使用该视图的查询。如果country2是派生 table,则也是如此,如下所示:

SELECT co2.Code, co2.UpperName, co2.OfficialLanguages,
COUNT(*) AS Cities
FROM
(
 SELECT co.Code, UPPER(co.Name) AS UpperName,
 COUNT(cl.Language) AS OfficialLanguages
 FROM country AS co JOIN countrylanguage AS cl
 ON cl.CountryCode=co.Code
 WHERE cl.isOfficial='T'
 GROUP BY co.Code
) AS co2
JOIN city ci ON ci.CountryCode = co2.Code
GROUP BY co2.Code;

功能依赖性的组合

MySQL 能够结合所有上述类型的功能依赖关系(基于键,基于相等性,基于视图)来验证更复杂的查询。