William Jiang

JavaScript,PHP,Node,Perl,LAMP Web Developer – http://williamjxj.com; https://github.com/williamjxj?tab=repositories

MySQL: update column by using regexp

1. MySQL, update column by using regexp

I want to update the values of a MySQL column, e.g. from:
http://www.example.com/news/ent//news/2002/09/02/ent-12.html?page=1223
to:
http://www.example.com/news/2002/09/02/ent-12.html?page=1223
For a huge table, if using:

  1. $ mysqldump -u user -p database table > dump.sql
  2. $ find /path/to/dump.sql -type f -exec sed -i ‘s/old_string/new_string/g’ {} \;
  3. $ mysqlimport -u user -p database table < dump.sql

It would make processing complex and easy to occur errors. My solution is very simple: just using MySQL’s substring(), concat(), they works very well: it takes around 10 seconds to process tens of thousands data.

update contents 
 set url=concat('http://www.example.com', substring(url,39)) 
  where createdby='gossip.cgi'

2. MySQL merge 2 tables

I have 2 MySql tables which have the same structure/indexes/unique index, each have tens of thousands data, and want to merge table2 into table1. The following is my way:

insert ignore into table1
  select * from table2;

I remember Oracle PL/SQL has the same syntax with MySQL for this kind of merge.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: