MySQL权限篇之REFERENCES以及外键级联操作

最新推荐文章于 2021-01-28 04:20:29 发布
最新推荐文章于 2021-01-28 04:20:29 发布
阅读量1.7w 收藏 17
点赞数 4
分类专栏： MySQL数据库-权限
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/q3dxdx/article/details/51005098
版权

REFERENCES，对象权限。

建立外键关系权限。

用户要在tb1上建立外键，外键指向tb2，那么该用户必须在tb2上有REFERENCES权限。

当然，还要有在tb1上alter的权限。

比如：

mysql> show grants for 'ut01'@'%';
+-----------------------------------------------------+
| Grants for ut01@%                                   |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO 'ut01'@'%'                    |
| GRANT REFERENCES ON `test`.`sys_menu` TO 'ut01'@'%' |
| GRANT ALTER ON `test`.`sys_role_menu` TO 'ut01'@'%' |
+-----------------------------------------------------+
3 rows in set (0.00 sec)


mysql>

在sys_menu上有references权限，在sys_role_menu上有alter权限。


来看该用户的操作：

C:\Users\Administrator>mysql -u'ut01'
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.11-log MySQL Community Server (GPL)


Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> use test
Database changed
mysql> ALTER TABLE `test`.`sys_role_menu`
    ->   ADD FOREIGN KEY (`rmenMenuId`) REFERENCES `test`.`sys_menu`(`menuId`) ON UPDATE CASCADE ON DELETE CASCADE;
ERROR 1142 (42000): ALTER command denied to user 'ut01'@'localhost' for table 'sys_role_menu'  #当该用户在sys_role_menu上没有alter权限时
mysql> ALTER TABLE `test`.`sys_role_menu`
    ->   ADD FOREIGN KEY (`rmenMenuId`) REFERENCES `test`.`sys_menu`(`menuId`) ON UPDATE CASCADE ON DELETE CASCADE;
Query OK, 4 rows affected (1.19 sec)
Records: 4  Duplicates: 0  Warnings: 0


mysql>

 

红色部分ON UPDATE CASCADE ON DELETE CASCADE;表示当父表列值变更时，子表的动作为级联更改。

ON UPDATE CASCADE 表示当父表更新时，子表也级联更新。

ON DELETE CASCADE 表示当父表删除时，子表也级联删除。


在定义外键关系时，父表的更新（或者删除）动作，可级联子表操作有如下四种定义方式：

CASCADE，表示子表列值随父表一起变化（更新也一起更新，删除也一起删除）。注意，子表上级联更改的列，不会激活触发器。

NO ACTION，表示父表列值被保护（阻止更改，不能有任何动作）。

RESTRICT，表示父表列值被保护（限制更改，不能有任何动作，效果和NO ACTION一致，其实就是NO ACTION的别名）。也是外键级联操作的默认行为。

SET NULL，表示父表列值被更改时，关联的子表列值置空（前提是子表该列属性可为NULL）。

对于非innodb表，还有一个方式是：SET DEFAULT，但是innodb表，该定义不合法。


下面是演示：


D:\temp>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.11-log MySQL Community Server (GPL)


Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> use test
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| sys_menu       |
| sys_role       |
| sys_role_menu  |
+----------------+
3 rows in set (0.00 sec)


mysql> SELECT
    ->   *
    -> FROM
    ->   `KEY_COLUMN_USAGE`
    -> WHERE table_schema = 'test'
    ->   AND REFERENCED_COLUMN_NAME IS NOT NULL;
ERROR 1146 (42S02): Table 'test.key_column_usage' doesn't exist
mysql> SELECT
    ->   *
    -> FROM
    ->   information_schema.`KEY_COLUMN_USAGE`
    -> WHERE table_schema = 'test'
    ->   AND REFERENCED_COLUMN_NAME IS NOT NULL;
Empty set (0.01 sec)
#表示test库没有目前还没有外键关系存在

mysql> select * from sys_menu;
+--------+--------------+-----------+
| menuId | menuParentId | menuUrlId |
+--------+--------------+-----------+
|    201 |            0 |      9001 |
|    202 |          201 |      9002 |
|    203 |          201 |      9003 |
|    204 |          202 |      9004 |
|    205 |          203 |      9005 |
|    206 |          204 |      9006 |
|    207 |          201 |      9007 |
|    208 |          202 |      9008 |
+--------+--------------+-----------+
8 rows in set (0.00 sec)


mysql> select * from sys_role_menu;
+--------+------------+------------+
| rmenId | rmenRoleId | rmenMenuId |
+--------+------------+------------+
|    301 |        101 |        201 |
|    302 |        101 |        202 |
|    303 |        101 |        205 |
|    304 |        102 |        206 |
+--------+------------+------------+
4 rows in set (0.00 sec)


mysql>

下面建立外键关系：

mysql> ALTER TABLE `test`.`sys_role_menu`
    ->   ADD FOREIGN KEY (`rmenMenuId`) REFERENCES `test`.`sys_menu`(`menuId`) ON UPDATE CASCADE;
Query OK, 4 rows affected (0.83 sec)
Records: 4  Duplicates: 0  Warnings: 0


mysql>

#已经添加父表更新，子表级联更新的外键。

当父表更新时：

mysql> select * from sys_menu;
+--------+--------------+-----------+
| menuId | menuParentId | menuUrlId |
+--------+--------------+-----------+
|    201 |            0 |      9001 |
|    202 |          201 |      9002 |
|    203 |          201 |      9003 |
|    204 |          202 |      9004 |
|    205 |          203 |      9005 |
|    206 |          204 |      9006 |
|    207 |          201 |      9007 |
|    208 |          202 |      9008 |
+--------+--------------+-----------+
8 rows in set (0.00 sec)


mysql> select * from sys_role_menu;
+--------+------------+------------+
| rmenId | rmenRoleId | rmenMenuId |
+--------+------------+------------+
|    301 |        101 |        201 |
|    302 |        101 |        202 |
|    303 |        101 |        205 |
|    304 |        102 |        206 |
+--------+------------+------------+
4 rows in set (0.00 sec)


mysql> update sys_menu set menuId=212 where menuId=202
Query OK, 1 row affected (0.12 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> select * from sys_menu;
+--------+--------------+-----------+
| menuId | menuParentId | menuUrlId |
+--------+--------------+-----------+
|    201 |            0 |      9001 |
|    203 |          201 |      9003 |
|    204 |          202 |      9004 |
|    205 |          203 |      9005 |
|    206 |          204 |      9006 |
|    207 |          201 |      9007 |
|    208 |          202 |      9008 |
|    212 |          201 |      9002 |
+--------+--------------+-----------+
8 rows in set (0.00 sec)


mysql> select * from sys_role_menu;
+--------+------------+------------+
| rmenId | rmenRoleId | rmenMenuId |
+--------+------------+------------+
|    301 |        101 |        201 |
|    302 |        101 |        212 |
|    303 |        101 |        205 |
|    304 |        102 |        206 |
+--------+------------+------------+
4 rows in set (0.00 sec)


mysql>

#可见，子表成功级联更新。这是CASCADE。

来看看NO ACTION：

mysql> ALTER TABLE `test`.`sys_role_menu`
    ->   DROP FOREIGN KEY `sys_role_menu_ibfk_1`;
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0   先删除原有的外键

mysql> ALTER TABLE `test`.`sys_role_menu`
    ->   ADD FOREIGN KEY (`rmenMenuId`) REFERENCES `test`.`sys_menu`(`menuId`) ON UPDATE NO ACTION;
Query OK, 4 rows affected (0.99 sec)
Records: 4  Duplicates: 0  Warnings: 0


mysql> select * from sys_menu;
+--------+--------------+-----------+
| menuId | menuParentId | menuUrlId |
+--------+--------------+-----------+
|    201 |            0 |      9001 |
|    203 |          201 |      9003 |
|    204 |          202 |      9004 |
|    205 |          203 |      9005 |
|    206 |          204 |      9006 |
|    207 |          201 |      9007 |
|    208 |          202 |      9008 |
|    212 |          201 |      9002 |
+--------+--------------+-----------+
8 rows in set (0.00 sec)


mysql> select * from sys_role_menu;
+--------+------------+------------+
| rmenId | rmenRoleId | rmenMenuId |
+--------+------------+------------+
|    301 |        101 |        201 |
|    302 |        101 |        212 |
|    303 |        101 |        205 |
|    304 |        102 |        206 |
+--------+------------+------------+
4 rows in set (0.00 sec)


mysql> update sys_menu set menuId=202 where menuId=212;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`sys_role_menu`, CONSTRAINT `sys_role_menu_ibfk_1` FOREIGN KEY (`rmenMenuId`) REFERENCES `sys_menu` (`menuId`) ON UPDATE NO ACTION)
mysql>

NO ACTION，在父表列值上不能有任何动作。


接下来轮到RESTRICT：

mysql> ALTER TABLE `test`.`sys_role_menu`
    ->   DROP FOREIGN KEY `sys_role_menu_ibfk_1`,
    ->   ADD FOREIGN KEY (`rmenMenuId`) REFERENCES `test`.`sys_menu`(`menuId`) ON UPDATE RESTRICT;
Query OK, 4 rows affected (1.63 sec)
Records: 4  Duplicates: 0  Warnings: 0


mysql> select * from sys_menu;
+--------+--------------+-----------+
| menuId | menuParentId | menuUrlId |
+--------+--------------+-----------+
|    201 |            0 |      9001 |
|    203 |          201 |      9003 |
|    204 |          202 |      9004 |
|    205 |          203 |      9005 |
|    206 |          204 |      9006 |
|    207 |          201 |      9007 |
|    208 |          202 |      9008 |
|    212 |          201 |      9002 |
+--------+--------------+-----------+
8 rows in set (0.00 sec)


mysql> select * from sys_role_menu;
+--------+------------+------------+
| rmenId | rmenRoleId | rmenMenuId |
+--------+------------+------------+
|    301 |        101 |        201 |
|    302 |        101 |        212 |
|    303 |        101 |        205 |
|    304 |        102 |        206 |
+--------+------------+------------+
4 rows in set (0.00 sec)


mysql> update sys_menu set menuId=202 where menuId=212;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`sys_role_menu`, CONSTRAINT `sys_role_menu_ibfk_2` FOREIGN KEY (`rmenMenuId`) REFERENCES `sys_menu` (`menuId`))
mysql>

被限制操作。父表列值被限制操作，效果和NO ACTION一致。也是默认的级联行为。

继续看看SET NULL：

mysql> ALTER TABLE `test`.`sys_role_menu`
    ->   DROP FOREIGN KEY `sys_role_menu_ibfk_2`,
    ->   ADD FOREIGN KEY (`rmenMenuId`) REFERENCES `test`.`sys_menu`(`menuId`) ON UPDATE SET NULL;
ERROR 1215 (HY000): Cannot add foreign key constraint
mysql> 

外键约束添加失败，原因是子表rmenMenuId列NOT NULL导致：

mysql> ALTER TABLE `test`.`sys_role_menu`
    ->   DROP FOREIGN KEY `sys_role_menu_ibfk_2`;
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> ALTER TABLE `test`.`sys_role_menu`
    ->   CHANGE `rmenMenuId` `rmenMenuId` BIGINT(20) NULL,
    ->   ADD FOREIGN KEY (`rmenMenuId`) REFERENCES `test`.`sys_menu`(`menuId`) ON UPDATE SET NULL;
Query OK, 4 rows affected (0.91 sec)
Records: 4  Duplicates: 0  Warnings: 0


mysql> select * from sys_menu;
+--------+--------------+-----------+
| menuId | menuParentId | menuUrlId |
+--------+--------------+-----------+
|    201 |            0 |      9001 |
|    203 |          201 |      9003 |
|    204 |          202 |      9004 |
|    205 |          203 |      9005 |
|    206 |          204 |      9006 |
|    207 |          201 |      9007 |
|    208 |          202 |      9008 |
|    212 |          201 |      9002 |
+--------+--------------+-----------+
8 rows in set (0.00 sec)


mysql> select * from sys_role_menu;
+--------+------------+------------+
| rmenId | rmenRoleId | rmenMenuId |
+--------+------------+------------+
|    301 |        101 |        201 |
|    302 |        101 |        212 |
|    303 |        101 |        205 |
|    304 |        102 |        206 |
+--------+------------+------------+
4 rows in set (0.00 sec)


mysql> update sys_menu set menuId=202 where menuId=212;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> select * from sys_menu;
+--------+--------------+-----------+
| menuId | menuParentId | menuUrlId |
+--------+--------------+-----------+
|    201 |            0 |      9001 |
|    202 |          201 |      9002 |
|    203 |          201 |      9003 |
|    204 |          202 |      9004 |
|    205 |          203 |      9005 |
|    206 |          204 |      9006 |
|    207 |          201 |      9007 |
|    208 |          202 |      9008 |
+--------+--------------+-----------+
8 rows in set (0.00 sec)


mysql> select * from sys_role_menu;
+--------+------------+------------+
| rmenId | rmenRoleId | rmenMenuId |
+--------+------------+------------+
|    301 |        101 |        201 |
|    302 |        101 |       NULL |
|    303 |        101 |        205 |
|    304 |        102 |        206 |
+--------+------------+------------+
4 rows in set (0.00 sec)


mysql> 

和文档描述一致。

q3dxdx
关注
  • 4
    点赞
  • 踩
  • 17
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
专栏目录
reference MySQL数据库_MySQL数据库基础
weixin_39771260的博客
01-19 1353
MySQL数据库基础本文的所有操作是基于CMD环境，MySQL通过在命令行中输入SQL语句对数据库进行操作。配置问题可参考《打通MySQL的操作权限》中的内容，该文算是针对前期的环境配置问题提供了一个较为全面的解决方案，与本文是一个相(yao)辅(xiang)相(hu)成(ying)的关系。MySQL官方文档访问链接https://dev.mysql.com/doc。一、连接相关1. 启动My...
mysql references关键字_mysql--一些关键字
weixin_31067561的博客
01-18 3789
primary key 和unique key在实际工作中，查看表设计总能看到这样的情况,一个primary，一个unique，还有一个key；CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,`uid` char(50) NOT NULL DEFAULT '' COMMENT 'uid',`type` mediumint(1) N...
mysql权限reference_MySQL权限详解
weixin_32027779的博客
01-28 2730
MySQL权限详解MySQL权限级别介绍MySQL权限级别全局性的管理权限，作用于整个MySQL实例级别数据库级别的权限，作用于某个指定的数据库上或者所有的数据库上数据库对象级别的权限，作用于指定的数据库对象上(表、视图等)或 者所有的数据库对象上权限存储在mysql库的user, db, tables_priv, columns_priv, and procs_priv这几个系统表中，待MySQ...
mysql 的references_mysql中 主键、外键、references的用法
weixin_35089891的博客
01-18 8530
1、PRIMARYKEY 约束1.1 表中经常有一个列或列的组合，其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键，通 过它可强制表的实体完整性。当创建或更改表时可通过定义PRIMARYKEY 约束来创建主键。一个表只能有一个PRIMARYKEY约束，而且PRIMARYKEY约束中的列不能接受空值。由于PRIMARY KEY约束确保唯一数据，所以经...
mysql - mysql资料教程 之 mysql references
开码河粉
01-11 664
1.下载mysql软件 https://dev.mysql.com/downloads/mysql/ 2.安装mysql (1)ubuntu安装mysql 5.7 https://www.cnblogs.com/kevin19931015/p/9206942.html (2)Ubuntu16.04下安装MySQL及简单操作 https://blog.csdn.net/qq_29666899/article/details/79079488 (3)ubuntu16.04 彻底卸...
MySQL使用外键实现级联删除与更新的方法
09-10
本篇文章将详细讲解如何使用外键在MySQL中实现级联删除和更新。 首先，要使用外键，数据库表必须使用支持外键的存储引擎，如InnoDB。InnoDB引擎提供了事务处理、行级锁定以及外键约束等功能，是实现这些级联操作的...
MySQL中利用外键实现级联删除、更新
11-07
"MySQL 中利用外键实现级联删除、更新" 在 MySQL 中，外键是指在一个表中的一列或多列，引用另一个表中的主键或唯一索引。外键可以强制实施数据的一致性和完整性，使得数据更加可靠。外键在 MySQL 中的实现主要是...
MySQL外键使用及说明详解
09-10
3. 级联操作：在外键上定义的级联规则（如ON DELETE CASCADE或ON UPDATE CASCADE）可以在删除或更新父表记录时自动更新或删除子表的相关记录。 外键的定义语法如下： ```sql [CONSTRAINT symbol] FOREIGN KEY [id]...
MySQL创建数据表并建立主外键关系详解
12-16
MySQL 创建数据表并建立主外键关系是数据库设计中的关键步骤，这有助于确保数据的一致性和完整性。在MySQL中，主键和外键是实现这种关系的重要工具。以下是关于这个主题的详细说明： 1. **主键**： 主键是表中的一...
mysql外键基本功能与用法详解
12-14
MySQL中的外键是一种重要的数据库设计概念，用于维护两个表之间的关系。外键是一个字段，它引用了另一个表的主键，从而确保数据的一致性和完整性。这篇文章将详细讲解MySQL中外键的基本功能和用法。 首先，理解外键...
MySQL 5.7 Reference Manual
09-02
MySQL 5.7 参考指南英文版 This is the MySQL™ Reference Manual. It documents MySQL 5.7 through 5.7.25, as well as NDB Cluster releases based on version 7.5 of NDB through 5.7.23-ndb-7.5.12, respectively. It may include documentation of features of MySQL versions that have not yet been released. For information about which versions have been released, see the MySQL 5.7 Release Notes. MySQL 5.7 features. This manual describes features that are not included in every edition of MySQL 5.7; such features may not be included in the edition of MySQL 5.7 licensed to you. If you have any questions about the features included in your edition of MySQL 5.7, refer to your MySQL 5.7 license agreement or contact your Oracle sales representative.
引用 mysql_在MYSQL中使用引用
weixin_29699727的博客
01-20 932
REFERENCES关键字是foreign key constraint的一部分,它使MySQL要求引用表的指定列中的值也存在于引用表的指定列中.这可以防止外键引用不存在或被删除的ID,并且可以选择阻止您在仍然引用行时删除它们.一个具体的例子是,如果每个员工都必须属于某个部门,那么您可以从employee.departmentid引用department.id添加外键约束.运行以下代码以创建两个测...
【MySQL】常用指令-权限 | 授权 | REFERENCES
hgSuper的博客
12-01 3293
一、授权 1）操作一：授权 grant select,create,drop,update,alter on *.* to '${username}'@'%' identified by '${password}' with grant option; eg: grant select,REFERENCES,create,drop,update,alter on *.* to 'root'@'%' identified by '123456' with grant option; 2）操作二：立即更
show mysql references
千点距
07-10 1014
// bookmark select concat(table_name, '.', column_name) as 'foreign key', concat(referenced_table_name, '.', referenced_column_name) as 'references' from information_schema.key_column_usage where refe
Mysql权限问题
fw1993的专栏
07-13 854
MySQL 赋予用户权限命令的简单格式可概括为： grant 权限 on 数据库对象 to 用户      一、grant普通数据用户，查询、插入、更新、删除数据库中所有表数据的权利。   grant select on testdb.* to common_user@'%' grant insert on testdb.* to common_user@'%' grant updat
[MySQL Reference Manual] 10 全球化
dida4700的博客
12-22 92
[MySQL Reference Manual] 10 全球化 10.全球化 本章主要介绍全球化，包含国际化和本地化，的一些问题： · MySQL在语句中支持的字符集 · 如何为服务配置不同的字符集 ·...
mysql reference
hopedown的博客
08-10 3285
mysql reference
MySql reference
天空 星座 海
04-01 698
MySql
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值