How to Update MySQL URLs Replacing Only the Domain and Path Segment?

Batch update MySQL URL: only replace the domain name and path part

This article introduces how to use MySQL's REPLACE function to modify the URL in the database table, replacing only the domain name and path parts, and retaining the file name.

Question:

Suppose there is a MySQL table containing two columns: id and url. The url column contains a URL similar to "http://domain1.example/images/img1.jpg". How can I update all URLs to "http://domain2.example/otherfolder/img1.jpg" while retaining the filename ?

Answer:

Execute the following SQL statement to complete the update:

<code class="language-sql">UPDATE urls
SET url = REPLACE(url, 'domain1.example/images/', 'domain2.example/otherfolder/')</code>

Explanation:

The
• UPDATE urls statement specifies the table name to be modified as urls.
The
• SET url = ... statement specifies updating the url column.
The
• REPLACE(url, 'domain1.example/images/', 'domain2.example/otherfolder/') function is used to replace part of the URL.
• REPLACEThe first parameter of the function is the original URL string.
• The second parameter is the substring to be replaced ('domain1.example/images/').
• The third parameter is the replacement string ('domain2.example/otherfolder/').
The
• REPLACE function finds the specified substring in each URL value and replaces it with a new substring, preserving the filename.

With this method, you can efficiently update a large number of URLs in the database, modifying only the specified domain name and path parts without affecting the file name.

The above is the detailed content of How to Update MySQL URLs Replacing Only the Domain and Path Segment?. For more information, please follow other related articles on the PHP Chinese website!