以下讨论提供了 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.CountryCode
,co.Code
连接结果中的值要么在匹配行中找到(由 确定
cl.CountryCode
),要么
NULL
在没有匹配项时补足(也由 确定cl.CountryCode
)。在每种情况下,此关系适用:
{cl.CountryCode} -> {co.Code}
cl.CountryCode
本身在功能上依赖于作为主键的
{ cl.CountryCode
,
} 。cl.Language
如果连接结果co.Code
是
NULL
-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;