William Jiang

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

MySQL: 4 tips

(1) update 1 table with other table’s values:
Instead of using cursor, mysql has a convenient way to update a table (row by row) by other table’s value. The following sql is to replace table t’s passwd field with table h’s passwd field value.

update users_prod as t, (select * from users_org) as h set
t.passwd=h.passwd
where t.id=h.id
and t.passwd is null

This is an efficient way, using (select …) as alias to fetch data into memory, then loop the memory to do the update.

(2) load data with pre-process:
The following is a more powerful load table with pre-process function, to update columns while loading.

mysql> LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE t
IGNORE 1 LINES
(@date,@time,@name,@weight_lb,@state)
SET
 dt = CONCAT(@date,' ',@time),
 firstname = SUBSTRING_INDEX(@name,' ',1),
 lastname = SUBSTRING_INDEX(@name,' ',-1),
 weight_kg = @weight_lb * .454,
 st_abbrev = (select abbrev from states where name = @state);

(3) create view:
create view can save a lot of codes and time to optimism application. The following are 2 examples: 1 is from Paul DuBois‘s book, 1 from http://dev.mysql.com/doc/refman/5.0/en/create-view.html:

 CREATE VIEW view_mail AS
  SELECT
  DATE_FORMAT(t, '%M %e, %Y') AS date_sent,
  CONCAT(from, '@', from_host) AS sender,
  CONCAT(to, '@', to_host) AS recipient, size
  FROM mail
create function book_subject
returns varchar(64) as
return @book_subject;
--
create view thematical_books as
select title, author
from books
where subject = book_subject();
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: