James John – Software Engineer

MySQL Default Column Value with Control Flow Operations

After a long while, I faced another problem which I solved and it feels awesome which I’m going to share right away! I must say, MySQL is awesome! The more you write MySQL the less you write PHP vice versa.

So on this post I’m going to share how to input default column value with conditions and related columns. In this example I am going to create a table users with age and name and stage.

Creating Table

CREATE TABLE <code>users</code> (
	<code>ID</code> INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    <code>name</code> VARCHAR( 200 ) NOT NULL,
	<code>age</code> INT( 3 ) NOT NULL,
    <code>stage</code> VARCHAR( 20 ) AS ( 
		CASE WHEN <code>age</code> BETWEEN 18 AND 22 THEN 
			'teenager' 
		WHEN <code>age</code> BETWEEN 23 AND 24 THEN 
			'youth' 
		WHEN <code>age</code> &gt;= 25 THEN 
			'adult' 
		ELSE 
			'kid' 
		END 
	)
)

This happens in the create query is, when a user is inserted and age specified, the life stage of the user is automatically calculated and inserted into the stage column. No extra work needed! Which is awesome.

Inserting Data

We are going to insert data into the table:

INSERT INTO <code>users</code> (<code>name</code>, <code>age</code>) VALUES ('Mark', '20');
INSERT INTO <code>users</code> (<code>name</code>, <code>age</code>) VALUES ('Okorie', '17');
INSERT INTO <code>users</code> (<code>name</code>, <code>age</code>) VALUES ('Seun', '23');
INSERT INTO <code>users</code> (<code>name</code>, <code>age</code>) VALUES ('Musa', '26');

Results

MariaDB [test]> SELECT * FROM users;
+----+--------+-----+----------+
| ID | name   | age | stage    |
+----+--------+-----+----------+
|  1 | Mark   |  20 | teenager |
|  2 | Okorie |  17 | kid      |
|  3 | Seun   |  23 | youth    |
|  4 | Musa   |  26 | adult    |
+----+--------+-----+----------+
4 rows in set (0.00 sec)

 

This also works with ALTER TABLE statement and updates existing rows and columns.

James John

Software Engineer