mysql set column sha2(uuid(),512) as column default value via trigger

发布时间 2023-12-09 23:01:03作者: FredGrit
mysql> show create table t3;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                   |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t3    | CREATE TABLE `t3` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `topic` varchar(255) NOT NULL COMMENT 'book topic',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> desc t3;
+-------------+-----------------+------+-----+-------------------+-------------------+
| Field       | Type            | Null | Key | Default           | Extra             |
+-------------+-----------------+------+-----+-------------------+-------------------+
| id          | bigint unsigned | NO   | PRI | NULL              | auto_increment    |
| create_time | datetime        | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| topic       | varchar(255)    | NO   |     | NULL              |                   |
+-------------+-----------------+------+-----+-------------------+-------------------+
3 rows in set (0.00 sec)

mysql> create trigger before_insert_t3 before insert on t3 for each row set new.topic=sha2(uuid(),512);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t3(id,create_time) select id,create_time from t2 limit 10;
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from t3;
+----+---------------------+----------------------------------------------------------------------------------------------------------------------------------+
| id | create_time         | topic                                                                                                                            |
+----+---------------------+----------------------------------------------------------------------------------------------------------------------------------+
|  1 | 2023-11-26 19:24:11 | c9591be56a93269d01ce265b7c26955a3a8c0b30c82c89ae2239f578ee3c797a370761d2cba5d02ce25033f35bd572aae093b34cf63ef7b1e7d3634cc3e59742 |
|  2 | 2023-11-26 19:24:11 | 2fcf18fc7ac4800ddc563b6838a3cb4cbc008e6763c6747d168031347283a27701260ea51be87f69010b80a1d1088dba78fe2e5c10d2e8526de03f5bd6f89ab1 |
|  3 | 2023-11-26 19:24:11 | d439f56c9ed522edaa10a5d10424c05d5b6cdc69568bb0d7f186c40e9c96be740e2d04f714e3fee0d4572bef9147b3bb7e49ec1ca3f7f637668be54ece0fc191 |
|  4 | 2023-11-26 19:24:11 | 85f318a17ce27e03aab0a2d5528f5fc38c866d5ba8ae6755d6ae870b92bdb1d2dc538c7eafe465296c651198c0ae3f6d79d247297bd7e600bd77d9dc7af92e7e |
|  5 | 2023-11-26 19:24:11 | 36757664c3128342397116bd8c9502c3dd073b85926107d1e749efeceea91836a4c22557af02c7d6c542f1a9bfc4cf3f653dc1c32a949556b18b58d870908e54 |
|  6 | 2023-11-26 19:24:11 | f1cf90b0da198d8f840291605d24b65ec8f8e7d6431631f82a4341c9e593e69762be4d9bf8c8bc9203feae8e3294dc6a3ea5725d0a4afc8adbdb4343bacddaaa |
|  7 | 2023-11-26 19:24:11 | d8d1cc98d62209dc94a05ebc4be377b084189191e49270322bc9e685043f01221c18c8b502fbc54c55a83a0dd2df21c55cadeceb87749080ec7830e295a57f32 |
|  8 | 2023-11-26 19:24:11 | 43bfa6d5dce261f72d1f9679304ff6f1a3d6bfc9002cc0ad58b3cce4df7f0c013dd889070b081ffda118979263f6825f0450b2f9b8fa460b7d6dfadb475f7448 |
|  9 | 2023-11-26 19:24:11 | a3fcf8ce4a816a79a3e3902c3456aa5f198bc4a3709f03c25c0bb3ff3484aff08510b0674b15beb6a532e9fe9024b8b31ff00ca10ccdbd51bfc4f06f1afe9065 |
| 10 | 2023-11-26 19:24:11 | 552900c24b66f841d87782b72d4d183c43fd8be0fb00bc7990de877f3755460d29fd4cd6001d069d0162770978bd55e4e07b7db615516f965f9c616ce7d80176 |
+----+---------------------+----------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

mysql> 

 

 

//create before trigger 

create trigger before_insert_t3 before insert on t3 for each row set new.topic=sha2(uuid(),512);
insert into t3(id,create_time) select id,create_time from t2 limit 10;