William Jiang

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

a MySQL procedure, and a tip

a MySQL procedure, and a tip

I wrote a simple MySQL stored-procedure to random set column value, it is interesting:

DELIMITER $$
create procedure cur_update()
begin
 declare a tinyint unsigned;
 declare b varchar(128);
 declare c int unsigned;

 declare curl2 CURSOR for select cid from contents 
   where updated > '2012-09-05';

 open curl2;

 LOOP
  fetch curl2 into c;
  select cid, name into a, b from categories order by rand() limit 1;  
  update contents ct 
    set ct.cate_id = a, ct.category = b
    where ct.cid = c;
end LOOP;

close curl2;
end$$

DELIMITER;

It works fine.
For a non-english database, if backup and recover, it might have problem without charset setting, should like this:


$ mysql -u root utf8_general_ci-db --default-character-set=utf8 < 
  utf8_general_ci-db.sql
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: