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?

What do you mean by "Automatically display"? A table holds data, that's all it does. You would have to write to `text` table and then also write to the `hashes` table with the hash of whatever you wrote into the `text` table. Later on you can join the two tables to display the `text` and the hash. Also... why store the `hash` in it's own table? — JNevill, Dec 04 '18 at 16:45
Well actually I guess it doens't matter what table the hashes are stored in. They can be stored in the same table. But, what I'm trying to prevent if having to create the hashes or having to insert them. Basically I just want to insert the text and then have MySQL generate the hash for that text. — ShadowAccount, Dec 04 '18 at 16:48
I just updated the question to include examples of exactly what's happening. Thanks! — ShadowAccount, Dec 04 '18 at 17:17
I believe you'll need a trigger to pull that off like `create trigger add_sha2 before insert on hashes for each row begin set new.hash = SHA2(text, 256);` or similar. Of course that comes with it's own caveats since now you have to maintain both a table and a trigger for that table. — JNevill, Dec 04 '18 at 18:05

Rohit Rasela · Accepted Answer · 2018-12-04T18:23:32.023

2

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

edited Dec 04 '18 at 18:23

answered Dec 04 '18 at 18:10

Rohit Rasela

425
3
6

score 2 · Answer 2 · answered Dec 04 '18 at 18:16

You don't need to declare your hash column as NOT NULL. It's based on another NOT NULL column, text, so the hash will naturally be NOT NULL as well.

You also have forgotten a closing parenthesis.

hash varchar(64) AS (SHA2(CONCAT(text), 256)   STORED,
                    1    2      3    3     2 ^

You need another closing paren where I indicated ^.

score 1 · Answer 3 · answered Nov 10 '21 at 10:47

If you already have the table filled by some content, you can Alter it with :

ALTER TABLE `page` ADD COLUMN `hash` char(64) AS (SHA2(`content`, 256)) AFTER `content`

This solution will add hash column right after the content one, make hash for existing and new records too. Unique index can be added to prevent insertion of large content duplicates.

Using MySQL to Generate SHA-256 Hashes?

3 Answers3

Linked