As I mentioned in an earlier post, InVision is upgrading some of its MySQL servers to 5.7.32 (for Long-Term Support, LTS). This upgrade brings with it some excited features like the JSON column type. It also unlocks the ability to add virtual columns to a table which can be derived from existing columns; and, as needed, get stored in a secondary index on said table. When I read about this feature, I immediately thought of the ability to derive email domain from a user’s email address. This is something that our data scientists and product teams are always asking about. As such, I wanted to sit down and see what a virtual “email domain column” might look like in MySQL 5.7.32 and Lucee CFML 5.3.7.47.

To lay the foundation for this exploration, let’s create a super simple user_account table that contains basic login information for a set of users. To start with, we’re just going to include email and password:

CREATE TABLE `user_account` (
	`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
	`email` varchar(75) NOT NULL,
	`password` varchar(35) NOT NULL,
	`createdAt` datetime NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `byEmail` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#sql #mysql #lucee