logo标签列表

更新MySQL表中特定记录

phpmysql
6

我正在处理电话系统，并需要与多个服务供应商合作。对于一个供应商，我有一个MySQL表格country_codes如下：

---------------------------------------------------------
country_code  |  area_code  |  country
---------------------------------------------------------
93            |  93         |  Afghanistan
0             |  9375       |  Afghanistan Cellular-AT
0             |  9370       |  Afghanistan Cellular-AWCC
355           |  355        |  Albania
0             |  35568      |  Albania Cellular-AMC
0             |  35567      |  Albania Cellular-Eagle
213           |  213        |  Algeria
0             |  21377      |  Algeria Cellular-Djezzy
0             |  2135       |  Algeria Cellular-Wataniya
---------------------------------------------------------

country_code这一列以前不存在，但由于我需要在PHP应用程序中使用它，所以我添加了它。我已经成功更新了一些记录的国家代码（使用我之前在这里得到的答案）。

我想要实现的是用相应的国家代码替换0。因此，表格应该如下所示 -

---------------------------------------------------------
country_code  |  area_code  |  country
---------------------------------------------------------
93            |  93         |  Afghanistan
93            |  9375       |  Afghanistan Cellular-AT
93            |  9370       |  Afghanistan Cellular-AWCC
355           |  355        |  Albania
355           |  35568      |  Albania Cellular-AMC
355           |  35567      |  Albania Cellular-Eagle
213           |  213        |  Algeria
213           |  21377      |  Algeria Cellular-Djezzy
213           |  2135       |  Algeria Cellular-Wataniya
---------------------------------------------------------

我希望我的表述足够清晰，您有什么想法可以使用PHP-MySQL实现吗？

(我不介意使用PHP代码来操作表格)

你可以利用区号和国家代码之间的关系来填充国家代码（区号的一部分是你的国家代码）。 - Naveen Kumar
我需要更多的信息。我们如何确定哪些是有效的国家代码？由于它们有时是两位数，有时是三位数，我们不能仅仅截取区号的末尾。您是否已经在您的28,000条记录中至少输入了所有有效的国家代码？ - Andrew
每个“公司”似乎都有一个以“国家”分配的区号开头的区号，对吗？这种模式适用于整个数据集吗？ - Tieson T.
@Sachyn country_code 和 area_code 是什么类型...是 int，varchar 吗？ - Michał Powaga
@ Michał Powaga 两者都是int类型 - skos
显示剩余2条评论
5个回答
6

请尝试执行以下查询：

UPDATE country_codes
SET country_code := @c := IF(@c IS NOT NULL AND country_code = 0, @c, country_code)
ORDER BY CAST(area_code AS CHAR)
国家代码可以是1位到4位数字。 - skos
SUBSTRING(area_code, 1, 3) 不会返回超过3个字符（数字）。如果area_code为空，它也将返回空字符串。你有空的area_code吗？ - Devart
1
+1，请问您能解释一下这个查询吗？我不理解@c部分的含义。 - Naveen Kumar
1
@Naveen Kumar - @c 是一个用户变量，我们按照特定的顺序遍历记录，并设置字段值和该变量。 - Devart
2
update cc set
    country_code = t.country_code
from country_codes cc
join (
    select country_code, country, char_length(trim(cast(country_code as char))) as code_len
    from country_codes
    where country_code <> 0
) t on 
    t.country_code = cast(substr(cast(cc.area_code as char), 1, t.code_len) as signed integer) and
    cc.country_code = 0 and
    cc.country like concat(t.country, '%')

我已经添加了cc.country like concat(t.country, '%')作为更加具体的条件，但它假设每个蜂窝网络名称都以其国家名称开头——如果不是这样，请省略它。
在@Sachyn评论后添加：
在SQLZOO上使用的测试代码很好用，仅用于测试，而不是更新查询：
select cc.*, t.country_code as new_country_code
from (
    select 93 as country_code, 93  as area_code , 'Afghanistan' as country union
    select 0  , 9375 , 'Afghanistan Cellular-AT' union
    select 0  , 9370 , 'Afghanistan Cellular-AWCC' union
    select 355, 355  , 'Albania' union
    select 0  , 35568, 'Albania Cellular-AMC' union
    select 0  , 35567, 'Albania Cellular-Eagle' union
    select 213, 213  , 'Algeria' union
    select 0  , 21377, 'Algeria Cellular-Djezzy' union
    select 0  , 2135 , 'Algeria Cellular-Wataniya'
) cc
join (
    select country_code, country, char_length(rtrim(cast(country_code as char))) as code_len
    from (
        select 93 as country_code, 93  as area_code , 'Afghanistan' as country union
        select 0  , 9375 , 'Afghanistan Cellular-AT' union
        select 0  , 9370 , 'Afghanistan Cellular-AWCC' union
        select 355, 355  , 'Albania' union
        select 0  , 35568, 'Albania Cellular-AMC' union
        select 0  , 35567, 'Albania Cellular-Eagle' union
        select 213, 213  , 'Algeria' union
        select 0  , 21377, 'Algeria Cellular-Djezzy' union
        select 0  , 2135 , 'Algeria Cellular-Wataniya'
    ) c
    where country_code <> 0
) t on 
    t.country_code = cast(substr(cast(cc.area_code as char), 1, t.code_len) as signed integer) and
    cc.country_code = 0 and
    cc.country like concat(t.country, '%')
谢谢Michal，我尝试了，但是它给了我这个错误`#1064-您的SQL语法有误;检查与您的MySQL服务器版本相对应的手册以使用正确的语法，位于第3行附近'from country_codes cc join（select country_code，country，char_length（trim （'。 - skos
鉴于所述的假设是正确的，这是正确的方法 - 您只需玩弄语法以使其工作。 - Stevo
@Sachyn 你是直接使用这个查询吗？我已经测试过了，应该可以正常工作。试试我的最新版本，也许期间有些变化。 - Michał Powaga
@MichałPowaga：我在想你的查询语句是否可以在更新中使用？ - Salman A
@SalmanA 是的，它会。我回答中的第一个查询（update）基于与第二个查询（select）相同的条件。Select 仅用于测试目的。 - Michał Powaga
1
如果您只需要一次性修复数据，可以尝试以下方法：
0）备份您的数据，或者更好的方法是在数据的副本上运行查询。
1）创建一个包含非零国家代码的表。我们需要单独的表，因为MySQL手册中指出：

目前，在子查询中不能更新表并从同一表中进行选择。

CREATE TABLE country_codes_list (
    country_code INT NOT NULL PRIMARY KEY
);

INSERT INTO country_codes_list
SELECT country_code
FROM country_codes
WHERE country_code <> 0;

2) 更新所有国家代码为0的行，方法是找到与区号开头匹配的国家代码：

UPDATE country_codes AS country_codes_zero SET country_code = (
    SELECT country_code
    FROM country_codes_list
    WHERE country_codes_list.country_code = SUBSTRING(country_codes_zero.area_code, 1, LENGTH(country_codes_list.country_code))
) WHERE country_code = 0;

这可能是一个非常慢的查询，因为它使用了一个相关子查询。但它应该一次性修复数据。

0

如果您有名为 "testdb" 的数据库和名为 "footable" 的表，那么应该可以像这样工作。

$link = mysql_connect("localhost", "username", "password1234");
mysql_select_db("testdb", $link);

$result = mysql_query("UPDATE footable SET country_code="93" WHERE country LIKE Afghanistan%", $link);
$result = mysql_query("UPDATE footable SET country_code="355" WHERE country LIKE Albania%", $link);

mysql_close($link);
1
我认为这与@Sachyn所要求的相反。 - Tieson T.
@Adam，那个表格中还有很多其他国家的数据，他所展示的只是样本数据。 - Naveen Kumar
@NaveenKumar 考虑一种情况，即国家为“UK-Orange”，国家代码为0，但唯一存在的条目是“英国”... 如果数据简单且可预测，则可以采用自动“猜测”方法 - 但这在现实世界中很少发生。 - Adam
0

您可以仅使用countries创建一个表，即：

INSERT INTO countries (country_code,country)
SELECT country_code,country FROM country_codes WHERE country_code=0

然后您可以轻松更新您的表格

UPDATE country_codes cc, countries c
SET cc.country_code = c.country_code
WHERE LOCATE(c.country,cc.country)=1 AND cc.country_code=0

我没有测试过，但我相信你可以理解要点。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接