Saturday, July 12, 2014

Control flow in MySQL conditional query



MySQL supports the IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT constructs for flow control within stored programs. It also supports RETURN within stored functions.
Suppose we have user table
CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `pwd` varchar(64) NOT NULL,
  `email` varchar(50) NOT NULL,
  `lasttime` date NOT NULL,
  `term` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
INSERT INTO `user` (`id`, `username`, `pwd`, `email`, `lasttime`, `term`) VALUES
(1, 'admin', '123456', 'admin@example.com', '2014-07-01', 'summer'),
(2, 'jiansen', '123456', 'jiansen@example.com', '2014-02-03', 'spring'),
(3, 'tom', '333333', 'tom@example.com', '2014-10-09', 'winter');

We want to convert lasttime (date format) to spring, summer or winter term.

1) SELECT in control flow in MySQL
SELECT  CASE 
     WHEN MONTH(DATE(lasttime))>= 1 AND MONTH(DATE(lasttime))<=4 THEN
       'spring'
     WHEN MONTH(DATE(lasttime))>= 5 AND MONTH(DATE(lasttime))<=8 THEN
        'summer'
     WHEN MONTH(DATE(lasttime))>= 9 AND MONTH(DATE(lasttime))<=12 THEN
        'winter' 
     END AS  Term
     FROM user

2) UPDATE in control flow in MySQL
UPDATE  user SET  term=CASE 
     WHEN MONTH(DATE(lasttime))>= 1 AND MONTH(DATE(lasttime))<=4 THEN
       'spring'
     WHEN MONTH(DATE(lasttime))>= 5 AND MONTH(DATE(lasttime))<=8 THEN
        'summer'
     WHEN MONTH(DATE(lasttime))>= 9 AND MONTH(DATE(lasttime))<=12 THEN
        'winter' 
     END 

Video: Example of control flow to select and update in MySQL

1 comment: