MYSQL conditional statement example (case when then)

MYSQL has very powerful set of control flow/conditional features, and case/when/then is one of these.
Assume that you need a table to store a generic value, with different value types, and for the sake of demo, let’s keep it simple. You have 3 types of values to store in this generic_table
type 1, string
type 2, integer
type 3, datetime

1. design and create this table

CREATE TABLE `generic_value` (
  `type` int(11) DEFAULT NULL,
  `string_value` varchar(45) DEFAULT NULL,
  `int_value` int(11) DEFAULT NULL,
  `time_value` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

2. insert some sample values

insert into generic_value(type, string_value) values (1, "hello world");
insert into generic_value(type, int_value) values (2, 9);
insert into generic_value(type, time_value) values (3, "20140726")

3. use the mysql conditional to select the corresponding value out based on the type

select type,
case type
	when 1 then string_value
	when 2 then int_value
	when 3 then time_value
end as value
from generic_value
Tagged with: , , , , ,
Posted in database, MyBatis

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>