需要 MySQL 查询：我需要从单个列中删除所有数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4958260/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Query needed: I need to delete all data from a single column
提问by Andy Smith
I have an ecommerce store that I am currently working on and there are approx 300 products which have a field named "product_url"
我有一家电子商务商店，目前正在开发中，大约有 300 种产品有一个名为“product_url”的字段
These fields contains an old url that I need to delete altogether.
这些字段包含我需要完全删除的旧 url。
How can I create a query that will replace all "product_url" fields with data in them witha null value?
如何创建一个查询，用空值替换所有“product_url”字段中的数据？
回答by FrustratedWithFormsDesigner
This will set every product_url to NULL which is currently not null.
这会将每个 product_url 设置为当前不为 null 的 NULL。
UPDATE table_name
SET product_url = NULL
WHERE product_url is not null;
回答by darioo
First of all, if you have 300 tables (one for each product), you cannot write one query that will set product URLs to NULL.
首先，如果您有 300 个表（每个产品一个），您不能编写一个将产品 URL 设置为 NULL 的查询。
You have to write a query for each table (UPDATE table_name SET product_url = NULLas others have already said).
您必须为每个表编写一个查询（UPDATE table_name SET product_url = NULL正如其他人已经说过的）。
And if you have 10,000 products one day, you will have 10,000 tables if you continue like this. This will become a maintenance nightmare since you can see now what kind of problems you have with 300 tables.
如果你一天有 10,000 种产品，如果你继续这样下去，你将有 10,000 张桌子。这将成为维护的噩梦，因为您现在可以看到 300 个表有什么样的问题。
Your database is denormalised. If your products share the same attributes, then they should be in one table named "Products", and every product should be represented as one row. Only then can you do what you wanted with one query.
您的数据库是非规范化的。如果您的产品具有相同的属性，那么它们应该在一个名为“Products”的表中，并且每个产品都应该表示为一行。只有这样，您才能通过一个查询做您想做的事。
回答by Niclas Lindqvist
Do you have a table for each product? If so I don't know.
你有每个产品的表吗？如果是这样我不知道。
Otherwise;
除此以外;
UPDATE products_table
SET product_url=null
回答by Jawa
Just an interpration...
只是一个解释...
UPDATE table_name SET column_name = NULL WHERE column_name = 'product_url'
回答by EthanM
Assuming ALL entries are to be filled with NULLs:
假设所有条目都用 NULL 填充：
UPDATE table_name SET product_url = NULL
回答by Blagovest Buyukliev
If you have lots of tables with the same prefix and the same structure, you could do a SHOW TABLES LIKE prefix_%. Then loop through the result set of this query and run separate queries to each table.
如果您有许多具有相同前缀和相同结构的表，则可以执行SHOW TABLES LIKE prefix_%. 然后遍历此查询的结果集并对每个表运行单独的查询。
回答by Ahmed Soliman
This will delete all data in that column without deleting the column itself .
这将删除该列中的所有数据而不删除该列本身。
UPDATE `table_name` SET `column_name`=null
