MySQL：删除所有记录中的数字前缀

Question

我有下面这个 page 表，希望删除 template_id 列中前缀为 '999' 的编号。

page_id    template_id    url
1          9991           a
2          9992           b
3          9993           c
4          4              d

所以我可以获取下面的更新数据，

page_id    template_id    url
1          1              a
2          2              b
3          3              c
4          4              d 

有什么办法可以去掉这种前缀吗？

- Run

---

它是“整数”类型。 - Run

Answer 1

为了获取您问题中所显示的数据：

SELECT
  page_id,
  SUBSTRING(template_id, IF(template_id RLIKE '^999', 4, 1)) AS template_id,
  url
FROM page

或者，如果您想永久更新表格：

UPDATE page
SET template_id = SUBSTRING(template_id, 4)
WHERE template_id RLIKE '^999'

MySQL的 隐式类型转换 将处理其余部分。

- eggyal

---

你能在 mysql 的整数列上使用字符串操作吗？我不确定。 - dqhendricks

请查看我的回答中的链接：它们将自动转换为字符串（在连接的字符集中，从5.5.3开始，或者在此之前作为“二进制”；无论哪种方式，我相信“SUBSTRING”和“RLIKE '^999'”都应该可以正常工作）。 - eggyal

Answer 2

如果您不想进行复杂的查询，或者需要进一步操作，可以执行以下操作：

while($r = mysql_fetch_assoc($q)) {
   $newTemplateID = (string) $r['template_id'];
   if (substr($newTemplateID,0,3) === 999 ) {
     $newTemplateID = substr($newTemplateID,3);

     mysql_query("UPDATE tbl 
                    SET template_id = {$newTemplateID} 
                    WHERE page_id = {$r['page_id']} 
                    LIMIT 1");
   }
}

- dynamic