如何选择所有具有列名称的表并更新该列 [英] How to select all tables with column name and update that column
问题描述
我想在数据库中找到所有包含列名Foo的表，并将其值更新为0，我在想这样的事情，但是我不知道如何将UPDATE放在该代码上，计划在MySQL数据库中的事件"中使用此语句，我使用的是WAMP，该想法基本上是每天运行一个事件，该事件将我的所有"Foo"列设置为0，而无需我手动进行
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name LIKE 'Foo'
否，不在单个语句中.
要获取所有包含名为Foo的列的表的名称，请执行以下操作:
SELECT table_schema, table_name
FROM information_schema.columns
WHERE column_name = 'Foo'
然后，您需要为每个表使用UPDATE语句. (可以在一个语句中更新多个表，但这需要(不必要的)交叉联接.)最好分别制作每个表.
您可以使用动态SQL在MySQL存储程序(例如PROCEDURE)中执行UPDATE语句
DECLARE sql VARCHAR(2000);
SET sql = 'UPDATE db.tbl SET Foo = 0';
PREPARE stmt FROM sql;
EXECUTE stmt;
DEALLOCATE stmt;
如果您为information_schema.tables中的select声明了一个游标，则可以使用游标循环为返回的每个table_name处理动态的UPDATE语句.
DECLARE done TINYINT(1) DEFAULT FALSE;
DECLARE sql VARCHAR(2000);
DECLARE csr FOR
SELECT CONCAT('UPDATE `',c.table_schema,'`.`',c.table_name,'` SET `Foo` = 0') AS sql
FROM information_schema.columns c
WHERE c.column_name = 'Foo'
AND c.table_schema NOT IN ('mysql','information_schema','performance_schema');
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN csr;
do_foo: LOOP
FETCH csr INTO sql;
IF done THEN
LEAVE do_foo;
END IF;
PREPARE stmt FROM sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP do_foo;
CLOSE csr;
(这只是示例的粗略概述，未经语法检查或测试.)
关注
一些简短的注释，其中一些想法可能在上面的答案中被掩盖了.
要获取包含列Foo的表的名称，我们可以从information_schema.columns表运行查询. (这是MySQL information_schema数据库中提供的表之一.)
因为我们可能在多个数据库中都有表，所以table_name不足以标识一个表；我们需要知道表在哪个数据库中.我们可以只引用表UPDATE db.mytable SET Foo...，而不是在运行UPDATE之前用"use db"语句进行处理.
我们可以使用查询information_schema.columns来继续并将所需为UPDATE语句创建的部分串在一起(串联)，并使SELECT返回我们需要运行以更新列<的实际语句. c0>，基本上是这样:
UPDATE `mydatabase`.`mytable` SET `Foo` = 0
但是我们想用table_schema和table_name中的值代替mydatabase和mytable.如果我们运行此SELECT
SELECT 'UPDATE `mydatabase`.`mytable` SET `Foo` = 0' AS sql
这将返回单行，其中包含单列(该列恰好被命名为sql，但该列的名称对我们而言并不重要).该列的值将只是一个字符串.但是，我们返回的字符串恰好是(我们希望)可以运行的SQL语句.
如果我们将那条线切成小块，然后使用CONCAT为我们将它们重新捆在一起，我们将得到相同的结果，例如
SELECT CONCAT('UPDATE `','mydatabase','`.`','mytable','` SET `Foo` = 0') AS sql
我们可以将该查询用作要针对information_schema.columns运行的语句的模型.我们将使用对information_schema.columns表中的列的引用替换'mydatabase'和'mytable'，这些列将为我们提供数据库和table_name.
SELECT CONCAT('UPDATE `',c.table_schema,'`.`',c.table_name,'` SET `Foo` = 0') AS sql
FROM information_schema.columns
WHERE c.column_name = 'Foo'
有些数据库我们肯定不是 想要更新... mysql，information_schema，performance_schema.我们要么需要将包含要更新的表的数据库列入白名单
AND c.table_schema IN ('mydatabase','anotherdatabase')
-或-我们需要将我们绝对不想更新的数据库列入黑名单
AND c.table_schema NOT IN ('mysql','information_schema','performance_schema')
我们可以运行该查询(如果希望以特定顺序返回行，则可以添加ORDER BY)，然后返回的是包含要运行的语句的列表.如果我们将这组字符串另存为纯文本文件(不包括标题行和其他格式)，并在每行末尾添加分号，则可以从mysql>命令行客户端执行一个文件./p>
(如果以上任何一项令人困惑，请告诉我.)
下一部分要复杂一些.其余的方法是将SELECT的输出另存为纯文本文件，然后从mysql命令行客户端执行语句的方法.
MySQL提供了一种设施/功能，使我们可以在MySQL存储程序(例如存储过程)的上下文中将 any 字符串作为SQL语句执行.即将使用的称为动态SQL .
要使用动态SQL ，我们使用语句PREPARE，EXECUTE和DEALLOCATE PREPARE. (取消分配不是绝对必要的，如果我们不使用它，MySQL会为我们清理，但我认为无论如何还是这样做是一个好习惯.)
动态SQL 仅在MySQL存储程序的上下文中可用.为此，我们需要一个包含要执行的SQL语句的字符串.举一个简单的例子，假设我们有这个:
DECLARE str VARCHAR(2000);
SET str = 'UPDATE mytable SET mycol = 0 WHERE mycol < 0';
要获取str的内容并作为SQL语句执行，其基本概述为:
PREPARE stmt FROM str;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
下一个复杂的部分是将其与我们正在运行的查询结合在一起，以获取要作为SQL语句执行的字符串值.为此，我们将光标循环放在一起.其基本概述是采用我们的SELECT语句:
SELECT bah FROM humbug
并将其转换为光标定义:
DECLARE mycursor FOR SELECT bah FROM humbug ;
我们想要执行的是，并遍历它返回的行.要执行该语句并准备一个结果集，我们打开"游标
OPEN mycursor;
完成后，我们就要发出关闭"命令来释放结果集，因此MySQL服务器知道我们不再需要它了，可以清理并释放分配的资源
CLOSE mycursor;
但是，在关闭游标之前，我们想在结果集中循环"，获取每一行，并对该行执行某些操作.我们用于将结果集中的下一行获取到过程变量中的语句是:
FETCH mycursor INTO some_variable;
在将行提取为变量之前，我们需要定义变量，例如
DECLARE some_variable VARCHAR(2000);
由于我们的游标(SELECT语句)仅返回单列，因此我们只需要一个变量.如果我们有更多列，则每个列都需要一个变量.
最终，我们将从结果集中获取最后一行.当我们尝试获取下一个时，MySQL将抛出错误.
其他编程语言将让我们只执行while循环，并让我们在处理完所有行后获取行并退出循环. MySQL更不可思议.进行循环:
mylabel: LOOP
-- do something
END LOOP mylabel;
这本身就构成了一个非常好的无限循环，因为该循环没有退出".幸运的是，MySQL提供了LEAVE语句作为退出循环的一种方式.通常，我们通常不想在第一次进入循环时退出循环，因此通常使用一些条件测试来确定是否已经完成，应该退出循环，还是应该结束循环，然后再解决再次循环.
mylabel: LOOP
-- do something useful
IF some_condition THEN
LEAVE mylabel;
END IF;
END LOOP mylabel;
在我们的例子中，我们想遍历结果集中的所有行，因此我们要在循环内的第一个语句中放置FETCH(这是我们想要做的事情).
要获得MySQL在尝试获取结果集最后一行时抛出的错误与条件测试之间的联系，我们必须确定是否应该离开...
MySQL为我们提供了一种在抛出错误时定义CONTINUE HANDLER(我们要执行的某些语句)的方法...
DECLARE CONTINUE HANDLER FOR NOT FOUND
我们要执行的操作是将变量设置为TRUE.
SET done = TRUE;
在运行SET之前，我们需要定义变量:
DECLARE done TINYINT(1) DEFAULT FALSE;
这样，我们可以更改LOOP以测试done变量是否设置为TRUE(作为退出条件)，因此我们的循环如下所示:
mylabel: LOOP
FETCH mycursor INTO some_variable;
IF done THEN
LEAVE mylabel;
END IF;
-- do something with the row
END LOOP mylabel;
要对行进行操作"是我们要获取some_variable的内容并对其进行一些有用的操作的地方.游标正在向我们返回一个我们想作为SQL语句执行的字符串.而MySQL为我们提供了动态SQL 功能，我们可以使用它来实现这一功能.
注意:MySQL具有有关过程中语句顺序的规则.例如，DECLARE语句必须放在开头.而且我认为，CONTINUE HANDLER必须是最后声明的东西.
同样: cursor 和动态SQL 功能仅在MySQL存储程序(例如存储过程)的上下文中可用.我上面给出的示例只是过程的 body 的示例.
要将其创建为存储过程，需要将其作为以下内容的一部分合并:
DELIMITER $$
DROP PROCEDURE IF EXISTS myproc $$
CREATE PROCEDURE myproc
NOT DETERMINISTIC
MODIFIES SQL DATA
BEGIN
-- procedure body goes here
END$$
DELIMITER ;
希望，这可以解释我更详细地说明的示例.
I want to find all the tables in my db that contain the column name Foo, and update its value to 0, I was thinking something like this, but I don't know how to place the UPDATE on that code, I plan on having this statement on the Events inside the MySQL database, I'm using WAMP, the idea is basically having an event run daily which sets all my 'Foo' Columns to 0 without me having to do it manually
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name LIKE 'Foo'
No, not in a single statement.
To get the names of all that tables that contain column named Foo:
SELECT table_schema, table_name
FROM information_schema.columns
WHERE column_name = 'Foo'
Then, you'd need an UPDATE statement for each table. (It's possible to do update multiple tables in a single statement, but that would need to be an (unnecessary) cross join.) It's better to do each table separately.
You could use dynamic SQL to execute the UPDATE statements in a MySQL stored program (e.g. PROCEDURE)
DECLARE sql VARCHAR(2000);
SET sql = 'UPDATE db.tbl SET Foo = 0';
PREPARE stmt FROM sql;
EXECUTE stmt;
DEALLOCATE stmt;
If you declare a cursor for the select from information_schema.tables, you can use a cursor loop to process a dynamic UPDATE statement for each table_name returned.
DECLARE done TINYINT(1) DEFAULT FALSE;
DECLARE sql VARCHAR(2000);
DECLARE csr FOR
SELECT CONCAT('UPDATE `',c.table_schema,'`.`',c.table_name,'` SET `Foo` = 0') AS sql
FROM information_schema.columns c
WHERE c.column_name = 'Foo'
AND c.table_schema NOT IN ('mysql','information_schema','performance_schema');
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN csr;
do_foo: LOOP
FETCH csr INTO sql;
IF done THEN
LEAVE do_foo;
END IF;
PREPARE stmt FROM sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP do_foo;
CLOSE csr;
(This is just an rough outline of an example, not syntax checked or tested.)
FOLLOWUP
Some brief notes about some ideas that were probably glossed over in the answer above.
To get the names of the tables containing column Foo, we can run a query from the information_schema.columns table. (That's one of the tables provided in the MySQL information_schema database.)
Because we may have tables in multiple databases, the table_name is not sufficient to identify a table; we need to know what database the table is in. Rather than mucking with a "use db" statement before we run an UPDATE, we can just reference the table UPDATE db.mytable SET Foo....
We can use our query of information_schema.columns to go ahead and string together (concatenate) the parts we need to create for an UPDATE statement, and have the SELECT return the actual statements we'd need to run to update column Foo, basically this:
UPDATE `mydatabase`.`mytable` SET `Foo` = 0
But we want to substitute in the values from table_schema and table_name in place of mydatabase and mytable. If we run this SELECT
SELECT 'UPDATE `mydatabase`.`mytable` SET `Foo` = 0' AS sql
That returns us a single row, containing a single column (the column happens to be named sql, but name of the column isn't important to us). The value of the column will just be a string. But the string we get back happens to be (we hope) a SQL statement that we could run.
We'd get the same thing if we broke that string up into pieces, and used CONCAT to string them back together for us, e.g.
SELECT CONCAT('UPDATE `','mydatabase','`.`','mytable','` SET `Foo` = 0') AS sql
We can use that query as a model for the statement we want to run against information_schema.columns. We'll replace 'mydatabase' and 'mytable' with references to columns from the information_schema.columns table that give us the database and table_name.
SELECT CONCAT('UPDATE `',c.table_schema,'`.`',c.table_name,'` SET `Foo` = 0') AS sql
FROM information_schema.columns
WHERE c.column_name = 'Foo'
There are some databases we definitely do not want to update... mysql, information_schema, performance_schema. We either need whitelist the databases containing the table we want to update
AND c.table_schema IN ('mydatabase','anotherdatabase')
-or- we need to blacklist the databases we definitely do not want to update
AND c.table_schema NOT IN ('mysql','information_schema','performance_schema')
We can run that query (we could add an ORDER BY if we want the rows returned in a particular order) and what we get back is list containing the statements we want to run. If we saved that set of strings as a plain text file (excluding header row and extra formatting), adding a semicolon at the end of each line, we'd have a file we could execute from the mysql> command line client.
(If any of the above is confusing, let me know.)
The next part is a little more complicated. The rest of this deals with an alternative to saving the output from the SELECT as a plain text file, and executin the statements from the mysql command line client.
MySQL provides a facility/feature that allows us to execute basically any string as a SQL statement, in the context of a MySQL stored program (for example, a stored procedure. The feature we're going to use is called dynamic SQL.
To use dynamic SQL, we use the statements PREPARE, EXECUTE and DEALLOCATE PREPARE. (The deallocate isn't strictly necessary, MySQL will cleanup for us if we don't use it, but I think it's good practice to do it anyway.)
Again, dynamic SQL is available ONLY in the context of a MySQL stored program. To do this, we need to have a string containing the SQL statement we want to execute. As a simple example, let's say we had this:
DECLARE str VARCHAR(2000);
SET str = 'UPDATE mytable SET mycol = 0 WHERE mycol < 0';
To get the contents of str evaluated and executed as a SQL statement, the basic outline is:
PREPARE stmt FROM str;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
The next complicated part is putting that together with the query we are running to get string value we want to execute as SQL statements. To do that, we put together a cursor loop. The basic outline for that is to take our SELECT statement:
SELECT bah FROM humbug
And turn that into a cursor definition:
DECLARE mycursor FOR SELECT bah FROM humbug ;
What we want to is execute that and loop through the rows it returns. To execute the statement and prepare a resultset, we "open" the cursor
OPEN mycursor;
When we're finished with it, we're goin to issue a "close", to release the resultset, so the MySQL server knows we don't need it anymore, and can cleanup, and free up the resources allocated to that.
CLOSE mycursor;
But, before we close the cursor, we want to "loop" through the resultset, fetching each row, and do something with the row. The statement we use to get the next row from the resultset into a procedure variable is:
FETCH mycursor INTO some_variable;
Before we can fetch rows into variables, we need to define the variables, e.g.
DECLARE some_variable VARCHAR(2000);
Since our cursor (SELECT statement) is returning only a single column, we only need one variable. If we had more columns, we'd need a variable for each column.
Eventually, we'll have fetched the last row from the result set. When we attempt to fetch the next one, MySQL is going to throw an error.
Other programming languages would let us just do a while loop, and let us fetch the rows and exit the loop when we've processed them all. MySQL is more arcane. To do a loop:
mylabel: LOOP
-- do something
END LOOP mylabel;
That by itself makes for a very fine infinite loop, because that loop doesn't have an "exit". Fortunately, MySQL gives us the LEAVE statement as a way to exit a loop. We typically don't want to exit the loop the first time we enter it, so there's usually some conditional test we use to determine if we're done, and should exit the loop, or we're not done, and should go around the the loop again.
mylabel: LOOP
-- do something useful
IF some_condition THEN
LEAVE mylabel;
END IF;
END LOOP mylabel;
In our case, we want to loop through all of the rows in the resultset, so we're going to put a FETCH a the first statement inside the loop (the something useful we want to do).
To get a linkage between the error that MySQL throws when we attempt to fetch past the last row in the result set, and the conditional test we have to determine if we should leave...
MySQL provides a way for us to define a CONTINUE HANDLER (some statement we want performed) when the error is thrown...
DECLARE CONTINUE HANDLER FOR NOT FOUND
The action we want to perform is to set a variable to TRUE.
SET done = TRUE;
Before we can run the SET, we need to define the variable:
DECLARE done TINYINT(1) DEFAULT FALSE;
With that we, can change our LOOP to test whether the done variable is set to TRUE, as the exit condition, so our loop looks something like this:
mylabel: LOOP
FETCH mycursor INTO some_variable;
IF done THEN
LEAVE mylabel;
END IF;
-- do something with the row
END LOOP mylabel;
The "do something with the row" is where we want to take the contents of some_variable and do something useful with it. Our cursor is returning us a string that we want to execute as a SQL statement. And MySQL gives us the dynamic SQL feature we can use to do that.
NOTE: MySQL has rules about the order of the statements in the procedure. For example the DECLARE statement have to come at the beginning. And I think the CONTINUE HANDLER has to be the last thing declared.
Again: The cursor and dynamic SQL features are available ONLY in the context of a MySQL stored program, such as a stored procedure. The example I gave above was only the example of the body of a procedure.
To get this created as a stored procedure, it would need to be incorporated as part of something like this:
DELIMITER $$
DROP PROCEDURE IF EXISTS myproc $$
CREATE PROCEDURE myproc
NOT DETERMINISTIC
MODIFIES SQL DATA
BEGIN
-- procedure body goes here
END$$
DELIMITER ;
Hopefully, that explains the example I gave in a little more detail.
这篇关于如何选择所有具有列名称的表并更新该列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
