Hash of two columns in mysql

Question

I have a MYSQL table, with 5 columns in it:

id bigint
name varchar
description varchar
slug

Can I get MySQL to automatically generate the value of slug as a 256 Bit Hash of name+description?

I am now using PHP to generate an SHA256 value of the slug prior to saving it.

Edit:

By automatic, I mean see if it's possible to change the default value of the slug field, to be a computed field that's the sha256 of name+description.

I already know how to create it as part of an insert operation.

Answer 1

MySQL 5.7 supports generated columns so you can define an expression, and it will be updated automatically for every row you insert or update.

CREATE TABLE IF NOT EXISTS MyTable (
  id int NOT NULL AUTO_INCREMENT,
  name varchar(50) NOT NULL,
  description varchar(50) NOT NULL,
  slug varchar(64) AS (SHA2(CONCAT(name, description), 256)) STORED NOT NULL,
  PRIMARY KEY (id)
) DEFAULT CHARSET=utf8;

If you use an earlier version of MySQL, you could do this with TRIGGERs:

CREATE TRIGGER MySlugIns BEFORE INSERT ON MyTable
FOR EACH ROW SET slug = SHA2(CONCAT(name, description));

CREATE TRIGGER MySlugUpd BEFORE UPDATE ON MyTable
FOR EACH ROW SET slug = SHA2(CONCAT(name, description), 256);

Answer 2

Beware that concat returns NULL if any one column in the input is NULL. So, to hash in a null-safe way, use concat_ws. For example:

select md5(concat_ws('', col_1, .. , col_n));