MySQL: 4 tips
Blogs20112011-06-29
(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 nullThis 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 mailcreate function book_subject
returns varchar(64) as
return @book_subject;
--
create view thematical_books as
select title, author
from books
where subject = book_subject();