功能依赖的检测
以下讨论提供了MySQL检测功能依赖关系的方式的几个示例。这些示例使用以下表示法:
{X} -> {Y}
理解这一点如“X
唯一地确定Y
,”这也意味着,Y
在功能上依赖于X
。
这些示例使用的world
数据库可以从https://dev.mysql.com/doc/index-other.html下载。您可以在同一页面上找到有关如何安装数据库的详细信息。
- 从键派生的功能依赖性
- 从多列键和相等性派生的功能依赖项
- 功能依赖特例
- 功能依赖关系和视图
- 功能依赖的组合
从键派生的功能依赖性
以下查询为每个国家/地区选择口语计数:
SELECT co.Name , COUNT(*)FROM countrylanguage cl, country coWHERE 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.0AS SpokenByFROM countrylanguage cl, country coWHERE 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.0AS SpokenByFROM countrylanguage clINNER JOIN country coON 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.0AS SpokenByFROM countrylanguage clLEFT JOIN country coON 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
补码,则因为co.Code
是主键,所以确定co.Name
。因此,在所有情况下:
{co.Code } -> {co.Name }
产生:
{cl.CountryCode, cl.Language } -> {cl.*,co.*}
结果,该查询是有效的。
但是,假设表被交换,如以下查询所示:
SELECT co.Name , cl.Language , cl.Percentage * co.Population/100.0AS SpokenByFROM country coLEFT JOIN countrylanguage clON 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 Country2AS SELECT co.Code , UPPER(co.Name )AS UpperName, COUNT(cl.Language )AS OfficialLanguagesFROM countryAS coJOIN countrylanguageAS clON 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 CitiesFROM country2AS co2JOIN city ciON ci.CountryCode = co2.Code GROUP BY co2.Code ;
该查询是有效的,因为如前所述:
{co2.Code } -> {co2.*}
MySQL能够发现视图结果中的功能依赖性,并使用它来验证使用该视图的查询。如果country2
是派生表(或公用表表达式),则也是如此,如下所示:
SELECT co2.Code , co2.UpperName, co2.OfficialLanguages, COUNT(*)AS CitiesFROM (SELECT co.Code , UPPER(co.Name )AS UpperName, COUNT(cl.Language )AS OfficialLanguagesFROM countryAS coJOIN countrylanguageAS clON cl.CountryCode=co.Code WHERE cl.isOfficial='T'GROUP BY co.Code )AS co2JOIN city ciON ci.CountryCode = co2.Code GROUP BY co2.Code ;
功能依赖的组合
MySQL能够结合所有上述类型的功能依赖关系(基于键,基于相等性,基于视图)来验证更复杂的查询。