Creating Row hash in SQL for Relational Data

Oct 5, 2019 sql hash

Introduction

How to create row hash for columns from multiple table in sql. There exists a lot of excryption and compression function in mysql https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html.

We shall use https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html#function_md5 in this example.

Create Database and Table structure

 1drop database if exists test_md5;
 2create database test_md5;
 3
 4use test_md5;
 5
 6CREATE TABLE if not exists `test_users` (
 7  `id` bigint(20) NOT NULL AUTO_INCREMENT,
 8  `username` varchar(255) not null,
 9  PRIMARY KEY (`id`)
10);
11
12CREATE TABLE if not exists `test_posts` (
13  `id` bigint(20) NOT NULL AUTO_INCREMENT,
14  `title` text NOT NULL, 
15  `user_id` bigint(20) NOT NULL,
16  PRIMARY KEY (`id`),
17  KEY `fk_post_user` (`user_id`),
18  CONSTRAINT `fk_post_user` FOREIGN KEY (`user_id`) REFERENCES `test_users` (`id`)
19);

Add dummy data to tables

 1-- Users 
 2insert into test_users(username) values('john');
 3insert into test_users(username) values('jane');
 4
 5-- Johntest_posts's Content
 6insert into test_posts(title, user_id) values('hey i am john, this is my very first post', 1);
 7insert into test_posts(title, user_id) values('hey i am john, this is my very first post', 1);
 8insert into test_posts(title, user_id) values('hey i am john again, i\'ll be going off from website for couple of days', 1);
 9-- Jane's Content
10insert into test_posts(title, user_id) values('hey i am jane, this website looks a biut dry', 2);

SQL Script to get row hash in relational table

1SELECT posts.id, posts.user_id, posts.title, users.username, md5(concat(posts.id, posts.title, users.username)) 
2as hash from test_posts as posts inner join test_users as users where posts.user_id = users.id

SQL Script to get direct JSON response from relational tables in mysql

1SELECT json_arrayagg(json_object('id', posts.id, 'title', posts.title, 'hash', md5(concat(posts.id, posts.title, users.username)))) 
2from test_posts as posts inner join test_users as users where posts.user_id = users.id

Response:

 1[{
 2	"id": 1,
 3	"hash": "cf22aad23f670ea2eba3a6d93d2d08cb",
 4	"title": "hey i am john, this is my very first post",
 5	"user_id": 1
 6}, {
 7	"id": 2,
 8	"hash": "9e3c222dcaf4171e6feb74e330c5a498",
 9	"title": "hey i am john, this is my very first post",
10	"user_id": 1
11}, {
12	"id": 3,
13	"hash": "4c5446afcc5c4da8270d7ae07a59ad85",
14	"title": "hey i am john again, i'll be going off from website for couple of days",
15	"user_id": 1
16}, {
17	"id": 4,
18	"hash": "d63478adec4c9821d523c0524243d72e",
19	"title": "hey i am jane, this website looks a biut dry",
20	"user_id": 2
21}]