Documentation Home
MySQL 8.0 参考手册  / 第 12 章函数和运算符  / 12.20聚合函数  /  12.20.4 函数依赖检测

12.20.4 函数依赖检测

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

{X} -> {Y}

将此理解为X唯一确定Y这也意味着它Y在功能上依赖于X

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

从键派生的功能依赖

以下查询为每个国家/地区选择了一定数量的口头语言:

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

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

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

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

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

从多列键和等式派生的功能依赖

此查询为每个国家/地区选择所有口头语言的列表以及使用这些语言的人数:

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.*}

结果,查询有效。

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

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条件中的相等性测试是对称的,但外部连接条件中的相等性测试不是对称的,因为表扮演不同的角色。

假设引用完整性被意外破坏,并且存在一行,而在 中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.CountryCodeco.Code连接结果中的值要么在匹配行中找到(由 确定 cl.CountryCode),要么 NULL在没有匹配项时补足(也由 确定cl.CountryCode)。在每种情况下,此关系适用:

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

cl.CountryCode本身在功能上依赖于作为主键的 { cl.CountryCode, } 。cl.Language

如果连接结果co.CodeNULL-complemented, co.Name也是如此。如果 co.Code不是 NULL-complemented,那么因为 co.Code是主键,它确定 co.Name。因此,在所有情况下:

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

哪个产量:

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

结果,查询有效。

但是,假设交换了表,如以下查询所示:

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.*}

所有NULL- 补充的行 cl被放入一个组(它们的两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,它也是组列,因此决定了所有其他选择的表达式:

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

MySQL 理解这一点并使用这些信息,如下所述。

此查询通过将视图与表连接起来,显示国家、他们有多少种不同的官方语言以及他们有多少个城市city

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是派生表,情况也是如此,如:

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 能够结合前面所有类型的函数依赖(基于键、基于相等、基于视图)来验证更复杂的查询。