主页 / dba / 问题 / 19468
Accepted
Tim Brigham
Asked: 2012-06-19 11:51:31 +0800 CST

MySQL - 自动更新对特定表的所有引用以在删除级联时使用？

  • 772

我在 MySQL 5.1 盒子上为我的一个软件包提供了一个特定的表，我需要删除几百个条目。这些是名称大写的用户名，应用程序是特定于大小写的并且期望小写。但是，该表被大量其他表引用，其中一些表可能引用回这些用户。

是否有现成的方法来调整我的数据库中引用表“登录”中给定列“用户名”的所有表的外键以设置删除级联属性？我看不到这样做的现成方法。

我需要的是根据此表使用外键更改所有其他表以利用删除级联。我希望这样做而不需要手动更改一百个表。

我正在使用的选择是

delete from user where user_name REGEXP '^.*[A-Z].+$';

问题是应用程序在 user_id 字段中创建了重复的用户 ID。我只想保留正确创建的用户名的 ID 号。

上面的选择正是我想要摆脱的目标。我无权访问创建这些条目的应用程序源。

mysql foreign-key

2 个回答

  1. Best Answer

    警告 ！！！

    在你做任何事情之前，mysqldump整个数据库或tarball /var/lib/mysql

    如果您担心区分大小写，则需要以特殊方式查找用户名

    我尝试了这个小实验

    use test
DROP TABLE rolando;
CREATE TABLE rolando
(id int not null auto_increment,
name varchar(20),primary key (id));
INSERT INTO rolando (name) VALUES
('rolando'),('Rolando'),
('ROLANDO'),('ROLANDO'),
('rolando'),('rolando');

    我加载了样本数据

    mysql> use test
Database changed
mysql> DROP TABLE rolando;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE rolando
    -> (id int not null auto_increment,
    -> name varchar(20),primary key (id));
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO rolando (name) VALUES
    -> ('rolando'),('Rolando'),
    -> ('ROLANDO'),('ROLANDO'),
    -> ('rolando'),('rolando');
Query OK, 6 rows affected (0.08 sec)
Records: 6  Duplicates: 0  Warnings: 0

    我运行了这些查询（请注意细微差别）

    mysql> SELECT name,COUNT(1) FROM rolando GROUP BY name;
+---------+----------+
| name    | COUNT(1) |
+---------+----------+
| rolando |        6 |
+---------+----------+
1 row in set (0.00 sec)

mysql> SELECT name,MD5(name),COUNT(1) FROM rolando GROUP BY name,MD5(name);
+---------+----------------------------------+----------+
| name    | MD5(name)                        | COUNT(1) |
+---------+----------------------------------+----------+
| ROLANDO | b3f7ba680fe83ab0b5174737e8d536a2 |        2 |
| Rolando | e0732d78dc135b8fcc33ec79bfa89d1f |        1 |
| rolando | e80d4ab77eb18a4ca350157fd487d7e2 |        3 |
+---------+----------------------------------+----------+
3 rows in set (0.00 sec)

mysql> SELECT name,MD5(name)=MD5(LOWER(name)) from rolando;
+---------+----------------------------+
| name    | MD5(name)=MD5(LOWER(name)) |
+---------+----------------------------+
| rolando |                          1 |
| Rolando |                          0 |
| ROLANDO |                          0 |
| ROLANDO |                          0 |
| rolando |                          1 |
| rolando |                          1 |
+---------+----------------------------+
6 rows in set (0.00 sec)

mysql> SELECT name,COUNT(1) FROM rolando GROUP BY name;
+---------+----------+
| name    | COUNT(1) |
+---------+----------+
| rolando |        6 |
+---------+----------+
1 row in set (0.00 sec)

mysql> SELECT name,(name LIKE lower(name)) like1,
    -> (name = binary LOWER(name)) like2 FROM rolando;
+---------+-------+-------+
| name    | like1 | like2 |
+---------+-------+-------+
| rolando |     1 |     1 |
| Rolando |     1 |     0 |
| ROLANDO |     1 |     0 |
| ROLANDO |     1 |     0 |
| rolando |     1 |     1 |
| rolando |     1 |     1 |
+---------+-------+-------+
6 rows in set (0.00 sec)

mysql>

    给出我的实验的这个视图，一个全大写或混合大小写的用户名可以等于一个全小写除非你使用BINARY操作符进行比较

    我不认为BINARY可以应用到ON DELETE CASCADE。

    但是，您可以这样做：要删除所有仅小写的用户名，请尝试运行以下操作之一：

    DELETE FROM logins WHERE username = BINARY LOWER(username);

    或者

    DELETE FROM logins WHERE MD5(username) = MD5(LOWER(username));

    如果您现在运行它，您可能会取消其他表中的所有用户名。改为运行它

    SET FOREIGN_KEY_CHECKS=0;
DELETE FROM logins WHERE username = BINARY LOWER(username);

    或者

    SET FOREIGN_KEY_CHECKS=0;
DELETE FROM logins WHERE MD5(username) = MD5(LOWER(username));

    您将必须转到其中包含用户名的所有表并执行

    SET FOREIGN_KEY_CHECKS=0;
DELETE FROM <whatevertable1> WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP '^.*[A-Z].+$';
DELETE FROM <whatevertable2> WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP '^.*[A-Z].+$';
DELETE FROM <whatevertable3> WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP '^.*[A-Z].+$';
.
.
.
DELETE FROM <whatevertableN> WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP '^.*[A-Z].+$';

    由于您有大量其他表，请使用 INFORMATION_SCHEMA 为您编写脚本：

    DELETE_SQLFILE=/root/SensitiveDelete.sql
MYSQL_CONN="-uroot -ppassword"
echo "SET FOREIGN_KEY_CHECKS=0;" > ${DELETE_SQLFILE}
mysql ${MYSQL_CONN} -ANe"SELECT CONCAT('DELETE FROM ',table_schema,'.',table_name,' WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP ''\^.*[A-Z].+$''\) FROM information_schema.columns WHERE column_name='user_name'" >> ${DELETE_SQLFILE}
cat ${DELETE_SQLFILE}

    如果文件对您来说是正确的，请像这样在 mysql 客户端中运行它

    mysql ${MYSQL_CONN} -A < ${DELETE_SQLFILE}

    试试看 ！！！

    • 3

  2. 如果您确实必须创建新行，则必须对每个表的每个相关行运行 UPDATE 并将相关行上的外键设置为 logins 表中新行的 ID。

    我不确定为什么有必要删除这些具有引用它们的数据的行。

    为什么不？

    • 您的应用程序可以调用存储过程，该存储过程将使用UPPER() 函数转换用户名和/或提供更深入的身份验证例程，而不是直接引用该“登录”表中的数据。
    • ...或者转换可能与应用程序已经运行的原始 SELECT 内联发生。
    • ...或在应用程序端进行转换。

    我认为除了破坏数据之外还有其他选择。

    如果万一此表的唯一用途是登录名并且用户名不是从属表中的 FK，则可以运行：

    CREATE TABLE logins_backup SELECT * FROM logins;    
UPDATE logins SET username = LOWER(username);

    这将使内容符合应用程序，但保持 PK ID 不变

    • 0

相关问题