MySQL更新所有以相同名称开头的列 [英] Mysql update all columns starting with same Name
问题描述
我有一个动态表,名为"products",具有多种语言. 表格列如下所示:
I have a dynamic table named 'products' with multiple Languages. The table columns looks like this:
id, product_id, store_de, store_en, store_fr, store_es... etc
语言可以更多或更少.
现在,我想更新此表并将所有以"store_"开头的列设置为值1.
Now I want to update this table and set all columns beginning with "store_" to value 1.
我尝试了以下操作:
$stmt = $dbh->prepare( "UPDATE products SET `store_%` = ? WHERE product_id = ?" );
$stmt->execute( array( 1, $lastID ) );
我收到以下错误消息:
SQLSTATE [42S22]:找不到列:1054中的未知列'store%' 字段列表"
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'store%' in 'field list'
是否可以更新以"store_"开头的所有列?还是必须列出所有列?
Is there a way to update all columns beginning with 'store_' or do I have to list all the columns?
根据jekaby的回答,这里是对我有用的完整解决方案:
Based on the answer from jekaby here is the full solution that worked for me:
$get_stores = $dbh->prepare("SHOW COLUMNS FROM products_active WHERE field REGEXP '^store'");
$get_stores->execute();
$stores = $get_stores->fetchAll();
$update_string = "";
$first_store = true;
foreach($stores as $store) {
if(!$first_store) {
$update_string .= ", ";
} else {
$first_store = false;
}
$update_string .= $store['Field']." = '".$status."'";
}
$update_activity = $dbh->prepare("UPDATE products_active SET $update_string WHERE product_id = ?");
$update_activity->execute(array($product_id));
推荐答案
您不能像这样store_%
-它是表中不存在的列,因为它是在您的错误中编写的.
You can not do like this store_%
- it is nonexistent column in table as it is written in your error.
您应该获得所有名称类似于/^store_/
(regexp)的列.然后更新列出了这些字段的所有这些字段.
You should get all colums with names like /^store_/
(regexp). And than update all this fields having listed them.
mysql> SHOW COLUMNS FROM products where field REGEXP '^store_'
比从字段"中收集所有字段...您可以了解更多有关如何获取所有列的信息
Than collect all fields from 'Field'... You can read more how to get all columns here.
这篇关于MySQL更新所有以相同名称开头的列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!