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.Code
是co
的主键,因此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.CountryCode
,cl.Language
)是cl
的两列复合主键,因此列对唯一确定cl
的所有列:
{cl.CountryCode, cl.Language} -> {cl.*}
此外,由于WHERE
子句中的相等性:
{cl.CountryCode} -> {co.Code}
并且,因为co.Code
是co
的主键:
{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.Code
是NULL
的补码,则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 理解并使用此信息,如下所述。
该查询通过将视图与city
table 结合在一起来显示国家/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 能够结合所有上述类型的功能依赖关系(基于键,基于相等性,基于视图)来验证更复杂的查询。