I am trying to create a table that has a column that hashes the id (not unique), the word and the the boolean on datetime valid. How can I hash int, string and boolean and then store it into table if it does not exist?
I have divided the problem into several step but I cant seem to put it all together:
I know how to hash:
SELECT crc32(concat(id, word, boolean)) FROM testhash;
Here is how I can check if the date is valid:
SELECT id FROM testhash WHERE valid > NOW();
Then I can insert the hash into table if it does not exist:
INSERT IGNORE INTO testhash (word,valid,hash) VALUES ('testword', STR_TO_DATE("2022-07-10", "%Y-%m-%d"), 'hereisthehash');
Here is how I try to create a table but it does not work:
CREATE TABLE testhash (
id int(11) NOT NULL AUTO_INCREMENT,
word varchar(150) NOT NULL,
valid datetime NOT NULL DEFAULT '0000-00-00',
hash varchar(64) AS (crc32(CONCAT(id, word))) STORED NOT NULL UNIQUE KEY,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
Tried to sqlfiddle but that failed too: http://sqlfiddle.com/#!9/9eecb/297837
During my attempt I tried to replicate the following:
