mysql中两列的哈希 [英] Hash of two columns in mysql

问题描述

我有一个MYSQL表，其中有5列:

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

id bigint
name varchar
description varchar
slug

我可以让MySQL自动生成slug的值作为name + description的256位哈希值吗?

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

我现在正在使用PHP在保存该段之前生成该段的SHA256值.

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

自动，我的意思是说，看看是否有可能将slug字段的默认值更改为具有name + description的sha256的计算字段.

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.

推荐答案

MySQL 5.7支持

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;

如果您使用的是早期版本的MySQL，则可以通过

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);

这篇关于mysql中两列的哈希的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！