Using MySQL to Generate SHA-256 Hashes?

Question

Here's what I'm trying to do:

CREATE TABLE IF NOT EXISTS hashes (
  id int NOT NULL AUTO_INCREMENT,
  text varchar(50) NOT NULL,
  hash varchar(64) NOT NULL AS (SHA2(CONCAT(text), 256) STORED,
  PRIMARY KEY (id)
) DEFAULT CHARSET=utf8;

And then I want to run an insert like this:

INSERT INTO `hashes` (`text`) VALUES ('testing');

From the research I've done, the id should be automatically generated since auto_increment is enabled, so I don't need to define it in the insert query.

From my CREATE TABLE query, the hash should be automatically generated based upon the data entered into the text field. However, when I run the CREATE TABLE command I get an error with this line:

hash varchar(64) NOT NULL AS (SHA2(CONCAT(text), 256) STORED

I'm just wanting the hash to be automatically generated similar to how CURRENT_TIMESTAMP will automatically generate the current time by default.

What am I doing wrong?

Answer 1

It seems you have syntax error. You should write NOT NULL after SHA2 hash function. Please try:

CREATE TABLE IF NOT EXISTS hashes (
  id int NOT NULL AUTO_INCREMENT,
  text varchar(50) NOT NULL,
  hash varchar(64) AS (SHA2(CONCAT(text), 256)) STORED  NOT NULL ,
  PRIMARY KEY (id)
) DEFAULT CHARSET=utf8;

INSERT INTO `hashes` (`text`) VALUES ('testing');