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
CSS Simple Menu Navigation Bar
ReplyDeletejQuery loop over JSON result
PHP MYSQL Advanced Search Feature
Simple Show Hide Menu Navigation
NodeJS Simple way to send SMTP mail
Simple pagination in PHP
Date Timestamp Formats in PHP