CODEWITHSUNDEEP
mysql
ShadowAccount
ShadowAccount

Reputation: 321

Using MySQL to Generate SHA-256 Hashes?

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?

Upvotes: 4

Views: 12563

Answers (3)

Meloman
Meloman

Reputation: 3712

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.

Upvotes: 1

Rohit Rasela
Rohit Rasela

Reputation: 445

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

Upvotes: 4

Bill Karwin
Bill Karwin

Reputation: 562230

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 ^.

Upvotes: 3

Related Questions