php - mysql如何将同一个表的一个字段的数据复制到另一个字段

Question

我的 mysql 表如下:

    id      host             url
--------------------------------------
    1                   test.com/index.php
    2                   domain.com/list
    3                   www.stackoverflow.com/questions/ask

我需要如下输出

    id      host                 url
------------------------------------------
    1     test.com             test.com/index.php
    2     domain.com           domain.com/list
    3     stackoverflow.com    www.stackoverflow.com/questions/ask

提前致谢。

Answer 1

使用SUBSTRING_INDEX(str, delim, count) :

UPDATE tablename
SET host = SUBSTRING_INDEX(url, '/', 1);

SQL Fiddle Demo

这将使您的表格看起来像:

| ID |                  HOST |                                 URL |
--------------------------------------------------------------------
|  1 |              test.com |                  test.com/index.php |
|  2 |            domain.com |                     domain.com/list |
|  3 | www.stackoverflow.com | www.stackoverflow.com/questions/ask |