需要根据列的内容更改单个列中的大量值

Question

我有一个数据库的列，其中包含完整的URL引用播客音频文件。几乎所有的文件都有http://abcdefgh.abcd.ca/podcasting/audio/YYYYMMDD-HHMMSS-to-YYYMMDD-HHMMSS.mp3格式。

问题是，我们现在在/audio/文件夹中有太多的文件，以至于在windows资源管理器中加载该文件夹需要很长时间。因此，我们希望按年将它们全部移动到文件夹中，例如http://abcdefgh.abcd.ca/podcasting/audio/20060321-140000-to-20000321-150000.mp3移动到http://abcdefgh.abcd.ca/podcasting/audio/2006/20060321-140000-to-20000321-150000.mp3，并且我们必须更新数据库中的所有urls以反映新的文件夹结构。我们有从2006年到2015年的文件，使用这种命名约定，在2015年9月左右，我们切换了，以便所有新的播客都自动使用year文件夹结构，不需要更新。

我对SQL相当缺乏经验-这是我工作的一部分，但当我被录用时，这是一个“很好的选择”。总之，我的尝试是这样的：

/*urls are in structure of = 'http://abcdefgh.abcd.ca/audio/YYYYMMDD-HHMMSS-to-YYYYMMDD-HHMMSS.mp3 but then some aren't in this format which we ignore. All urls are in the column of the table named "url"*/
UPDATE table.podcast_episodes
SET url = CASE 
    WHEN url LIKE 'http://abcdefgh.abcd.ca/podcasting/audio/2006____-______-to-%.mp3' THEN url = concat(substring(url,1,41),'2006/',substring(url FROM 42))
    WHEN url LIKE 'http://abcdefgh.abcd.ca/podcasting/audio/2007____-______-to-%.mp3' THEN url = concat(substring(url,1,41),'2007/',substring(url FROM 42))
    WHEN url LIKE 'http://abcdefgh.abcd.ca/podcasting/audio/2008____-______-to-%.mp3' THEN url = concat(substring(url,1,41),'2008/',substring(url FROM 42))
    WHEN url LIKE 'http://abcdefgh.abcd.ca/podcasting/audio/2009____-______-to-%.mp3' THEN url = concat(substring(url,1,41),'2009/',substring(url FROM 42))
    WHEN url LIKE 'http://abcdefgh.abcd.ca/podcasting/audio/2010____-______-to-%.mp3' THEN url = concat(substring(url,1,41),'2010/',substring(url FROM 42))
    WHEN url LIKE 'http://abcdefgh.abcd.ca/podcasting/audio/2011____-______-to-%.mp3' THEN url = concat(substring(url,1,41),'2010/',substring(url FROM 42))
    WHEN url LIKE 'http://abcdefgh.abcd.ca/podcasting/audio/2012____-______-to-%.mp3' THEN url = concat(substring(url,1,41),'2011/',substring(url FROM 42))
    WHEN url LIKE 'http://abcdefgh.abcd.ca/podcasting/audio/2013____-______-to-%.mp3' THEN url = concat(substring(url,1,41),'2012/',substring(url FROM 42))
    WHEN url LIKE 'http://abcdefgh.abcd.ca/podcasting/audio/2014____-______-to-%.mp3' THEN url = concat(substring(url,1,41),'2014/',substring(url FROM 42))
    WHEN url LIKE 'http://abcdefgh.abcd.ca/podcasting/audio/2015____-______-to-%.mp3' THEN url = concat(substring(url,1,41),'2015/',substring(url FROM 42))
    /* do nothing */
    ELSE url= url
END

它的问题是，当URL字段与case语句匹配时，它只是将URL字段设置为0，或者当它命中"else“语句时，将URL字段设置为1，这不是我想要的。

Answer 1

因为您没有正确使用ELSE或CASE语句。格式为

CASE WHEN url LIKE ... THEN (the concatenated value that you're setting url to).  

你不需要做

THEN url = (concatenated value you're creating)

因为它会像你看到的那样失败。如果您像这样做了轻微的修改：

UPDATE podcast_episodes
SET url = CASE 
WHEN url LIKE 'http://abcdefgh.abcd.ca/podcasting/audio/2006____-______-to-%.mp3' THEN concat(substring(url,1,41),'2006/',substring(url FROM 42))
WHEN url LIKE 'http://abcdefgh.abcd.ca/podcasting/audio/2007____-______-to-%.mp3' THEN concat(substring(url,1,41),'2007/',substring(url FROM 42))
WHEN url LIKE 'http://abcdefgh.abcd.ca/podcasting/audio/2008____-______-to-%.mp3' THEN concat(substring(url,1,41),'2008/',substring(url FROM 42))
WHEN url LIKE 'http://abcdefgh.abcd.ca/podcasting/audio/2009____-______-to-%.mp3' THEN concat(substring(url,1,41),'2009/',substring(url FROM 42))
WHEN url LIKE 'http://abcdefgh.abcd.ca/podcasting/audio/2010____-______-to-%.mp3' THEN concat(substring(url,1,41),'2010/',substring(url FROM 42))
WHEN url LIKE 'http://abcdefgh.abcd.ca/podcasting/audio/2011____-______-to-%.mp3' THEN concat(substring(url,1,41),'2010/',substring(url FROM 42))
WHEN url LIKE 'http://abcdefgh.abcd.ca/podcasting/audio/2012____-______-to-%.mp3' THEN concat(substring(url,1,41),'2011/',substring(url FROM 42))
WHEN url LIKE 'http://abcdefgh.abcd.ca/podcasting/audio/2013____-______-to-%.mp3' THEN concat(substring(url,1,41),'2012/',substring(url FROM 42))
WHEN url LIKE 'http://abcdefgh.abcd.ca/podcasting/audio/2014____-______-to-%.mp3' THEN concat(substring(url,1,41),'2014/',substring(url FROM 42))
WHEN url LIKE 'http://abcdefgh.abcd.ca/podcasting/audio/2015____-______-to-%.mp3' THEN concat(substring(url,1,41),'2015/',substring(url FROM 42))
/* do nothing */
ELSE url
END

那么它应该是有效的。Here's a SQL fiddle to show the difference

Answer 2

如果这是一次性工作，则不需要使用SQL。您可以转储该表：

mysqldump -u user -p database table > dump.sql

进行文本替换：

sed -i 's|\(http://abcdefgh.abcd.ca/podcasting/audio/\)\([0-9]\{4\}\)\([-to0-9]*\.mp3\)|\1\2/\2\3|g' dump.sql

并将该表重新导入：

mysqlimport -u user -p database table < dump.sql

这些命令适用于Linux/Unix。如果你使用的是Windows，你可以用你最喜欢的工具来做同样的事情。