Mysql SQL 请求：REPLACE 和通配符 %.如何做正确的事

这可以通过简单的字符串替换方法来实现--CONCAT、SUBSTRING、LOCATE、REPLACE.前3个查询显示了循序渐进的逻辑，而查询#4则将它们放在一起.

These queries assume 100 is the same for all URLs. If that's not the case, you could use similar logic to detect the position of 101 and use 102 from there.

实际更新表的最后一个查询(查询#6；请注意，在此之后可能存在重复的URL，并且可以通过下面的查询#5提前检测到):

UPDATE
  test
SET
  url = CONCAT(
    'http://example.com/tag/',
    SUBSTRING(
      REPLACE(
        url,
        'http://example.com/component/tags/tag/',
        ''
      ),
      LOCATE(
        '-',
        REPLACE(
          url,
          'http://example.com/component/tags/tag/',
          ''
        )
      ) + 1
    )
  );

Schema (MySQL v5.7)个

CREATE TABLE test (
  id INT,
  url TEXT
);
INSERT INTO test (id, url) VALUES (1, 'http://example.com/component/tags/tag/15-thing.html');
INSERT INTO test (id, url) VALUES (2, 'http://example.com/component/tags/tag/16-another.html');
INSERT INTO test (id, url) VALUES (3, 'http://example.com/component/tags/tag/9999-texttext.html');
INSERT INTO test (id, url) VALUES (4, 'http://example.com/component/tags/tag/9999-more-and-more-and-more-and-more-text.html');
INSERT INTO test (id, url) VALUES (5, 'http://example.com/component/tags/tag/6534562-thing.html');
INSERT INTO test (id, url) VALUES (6, 'http://example.com/component/tags/tag/0-thing.html');
INSERT INTO test (id, url) VALUES (7, 'http://example.com/component/tags/tag/1-sometext.html');
INSERT INTO test (id, url) VALUES (8, 'http://example.com/component/tags/tag/2-anothertext.html');
INSERT INTO test (id, url) VALUES (9, 'http://example.com/component/tags/tag/999-moreanothertext.html');

Query #1 - remove the known/standard text before the filename (e.g. 15-thing.html)个

SELECT
  REPLACE(
    url,
    'http://example.com/component/tags/tag/',
    ''
  ) as variable_text
FROM test;

Query #2 - find the index of - in the remaining string (e.g. 3)个

SELECT
  LOCATE(
    '-',
    REPLACE(
      url,
      'http://example.com/component/tags/tag/',
      ''
    )
  ) as hyphen_index
FROM test;

Query #3 - remove everything after that index (e.g. thing.html)个

SELECT
  SUBSTRING(
    REPLACE(
      url,
      'http://example.com/component/tags/tag/',
      ''
    ),
    LOCATE(
      '-',
      REPLACE(
        url,
        'http://example.com/component/tags/tag/',
        ''
      )
    ) + 1
  ) as filename
FROM test;

Query #4 - construct the full URL (e.g. 100)

SELECT
  CONCAT(
    'http://example.com/tag/',
    SUBSTRING(
      REPLACE(
        url,
        'http://example.com/component/tags/tag/',
        ''
      ),
      LOCATE(
        '-',
        REPLACE(
          url,
          'http://example.com/component/tags/tag/',
          ''
        )
      ) + 1
    )
  ) as new_url
FROM test;

Query #5 - detect duplicates ahead of actually updating the table个

SELECT GROUP_CONCAT(id) as duplicate_ids, new_url FROM (
  SELECT
    id,
    CONCAT(
      'http://example.com/tag/',
      SUBSTRING(
        REPLACE(
          url,
          'http://example.com/component/tags/tag/',
          ''
        ),
        LOCATE(
          '-',
          REPLACE(
            url,
            'http://example.com/component/tags/tag/',
            ''
          )
        ) + 1
      )
    ) as new_url
  FROM test) as new_urls
GROUP BY new_url
HAVING COUNT(*) > 1;

Query #6 - actually update the table个

UPDATE
  test
SET
  url = CONCAT(
    'http://example.com/tag/',
    SUBSTRING(
      REPLACE(
        url,
        'http://example.com/component/tags/tag/',
        ''
      ),
      LOCATE(
        '-',
        REPLACE(
          url,
          'http://example.com/component/tags/tag/',
          ''
        )
      ) + 1
    )
  );

没有要显示的结果.

Query #7 - view the new URLs个

SELECT * FROM test;

View on DB Fiddle个