重命名MySQL中的重复行 [英] Rename duplicate rows in MySQL
问题描述
CREATE TABLE如果不存在`products`(
`product_id` int(11)unsigned NOT NULL AUTO_INCREMENT,
`product_code` varchar(32)NOT NULL,
PRIMARY KEY(`product_id`)
)ENGINE = MyISAM DEFAULT CHARSET = utf8 AUTO_INCREMENT = 4;
INSERT INTO`products`(`product_id`,`product_code`)VALUES
(1,'A'),
(2,'B'),
(3,'A');
这里第一行和第三行有相同的项目。我只是想添加一个后缀,如复制。结果将是:
product_id product_code
----------- --- -----------
1 A
2 B
3 A复制
那么如何在MySQL中解决这个问题?
编辑:上面的数据就是一个例子。我的原始表格有3000多个产品。
如果你可以使用PHP,我建议你通过PHP来做。我没有找到一种方法来做这个与MySQL。这个将会更新所有的行数,其中包含原始数据。
更新表
SET product_code = CONCAT product_code,'Copy')
GROUP BY product_code
HAVING COUNT(*)> 1
你不想要的。所以,如果你使用php,你可以这样做(假设你的表中的行数很少(3000是罚款))
<?php
$ result = mysql_query(SELECT * FROM table);
$ rowsCnt = array();
while($ row = mysql_fetch_assoc($ result)){
$ rows [] = $ row;
$ rowsCnt [$ row ['product_code']] ++;
}
foreach($ rows as $ index => $ row){
if($ rowsCnt [$ row ['product_code']]> 1){
mysql_query(UPDATE table SET product_code ='.mysql_real_escape_string($ row ['product_code'])复制LIMIT($ rowsCnt [$ row ['product_code']] - 1)
}
免责声明:未经测试!首先!
There are some similar topics in stackoverflow, but still I didn't succeed to rename my duplicate rows:
CREATE TABLE IF NOT EXISTS `products` (
`product_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`product_code` varchar(32) NOT NULL,
PRIMARY KEY (`product_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
INSERT INTO `products` (`product_id`, `product_code`) VALUES
(1, 'A'),
(2, 'B'),
(3, 'A');
Here first and third rows have same items. I just want to add a suffix like "Copy". And the result would be:
product_id product_code
----------- --------------
1 A
2 B
3 A Copy
So how can I solve this problem in MySQL?
Edit: Data above is an example. My original table has more than 3000 products.
If you can use PHP, I would recommend you to do it via PHP. I haven't found a way to do this with MySQL. This one will update ALL rows having count > 1, including the original.
UPDATE table
SET product_code = CONCAT(product_code, ' Copy')
GROUP BY product_code
HAVING COUNT(*) > 1
which you don't want. So, if you use php, you can do this (assuming you have low number of rows in your table (3000 is fine))
<?php
$result = mysql_query("SELECT * FROM table");
$rowsCnt = array();
while($row = mysql_fetch_assoc($result)){
$rows[] = $row;
$rowsCnt[ $row['product_code'] ]++;
}
foreach($rows as $index => $row) {
if ($rowsCnt[ $row['product_code'] ] > 1) {
mysql_query("UPDATE table SET product_code = '".mysql_real_escape_string($row['product_code'])." Copy' LIMIT ".($rowsCnt[ $row['product_code'] ] - 1)
}
}
Disclaimer: Not tested! Make a backup first!
这篇关于重命名MySQL中的重复行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!