Skip to content

Instantly share code, notes, and snippets.

@mahadirz

mahadirz/database_import.sql

Created January 10, 2022 01:26
Show Gist options
  • Save mahadirz/86ec2bb6f9ce52124b1baef0f563c487 to your computer and use it in GitHub Desktop.
Save mahadirz/86ec2bb6f9ce52124b1baef0f563c487 to your computer and use it in GitHub Desktop.
Download ZIP
binlog_tutorial
Raw
database_import.sql
# credit to https://www.digitalocean.com/community/tutorials/working-with-json-in-mysql
CREATE DATABASE IF NOT EXISTS `e_store`
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;
SET default_storage_engine = INNODB;
CREATE TABLE `e_store`.`brands`(
`id` INT UNSIGNED NOT NULL auto_increment ,
`name` VARCHAR(250) NOT NULL ,
PRIMARY KEY(`id`)
);
CREATE TABLE `e_store`.`categories`(
`id` INT UNSIGNED NOT NULL auto_increment ,
`name` VARCHAR(250) NOT NULL ,
PRIMARY KEY(`id`)
);
INSERT INTO `e_store`.`brands`(`name`)
VALUES
('Samsung');
INSERT INTO `e_store`.`brands`(`name`)
VALUES
('Nokia');
INSERT INTO `e_store`.`brands`(`name`)
VALUES
('Canon');
INSERT INTO `e_store`.`categories`(`name`)
VALUES
('Television');
INSERT INTO `e_store`.`categories`(`name`)
VALUES
('Mobile Phone');
INSERT INTO `e_store`.`categories`(`name`)
VALUES
('Camera');
CREATE TABLE `e_store`.`products`(
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(250) NOT NULL ,
`brand_id` INT UNSIGNED NOT NULL ,
`category_id` INT UNSIGNED NOT NULL ,
`attributes` JSON NOT NULL ,
PRIMARY KEY(`id`) ,
INDEX `CATEGORY_ID`(`category_id` ASC) ,
INDEX `BRAND_ID`(`brand_id` ASC) ,
CONSTRAINT `brand_id` FOREIGN KEY(`brand_id`) REFERENCES `e_store`.`brands`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE ,
CONSTRAINT `category_id` FOREIGN KEY(`category_id`) REFERENCES `e_store`.`categories`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE
);
INSERT INTO `e_store`.`products`(
`name` ,
`brand_id` ,
`category_id` ,
`attributes`
)
VALUES(
'Prime' ,
'1' ,
'1' ,
'{"screen": "50 inch", "resolution": "2048 x 1152 pixels", "ports": {"hdmi": 1, "usb": 3}, "speakers": {"left": "10 watt", "right": "10 watt"}}'
);
INSERT INTO `e_store`.`products`(
`name` ,
`brand_id` ,
`category_id` ,
`attributes`
)
VALUES(
'Octoview' ,
'1' ,
'1' ,
'{"screen": "40 inch", "resolution": "1920 x 1080 pixels", "ports": {"hdmi": 1, "usb": 2}, "speakers": {"left": "10 watt", "right": "10 watt"}}'
);
INSERT INTO `e_store`.`products`(
`name` ,
`brand_id` ,
`category_id` ,
`attributes`
)
VALUES(
'Dreamer' ,
'1' ,
'1' ,
'{"screen": "30 inch", "resolution": "1600 x 900 pixles", "ports": {"hdmi": 1, "usb": 1}, "speakers": {"left": "10 watt", "right": "10 watt"}}'
);
INSERT INTO `e_store`.`products`(
`name` ,
`brand_id` ,
`category_id` ,
`attributes`
)
VALUES(
'Bravia' ,
'1' ,
'1' ,
'{"screen": "25 inch", "resolution": "1366 x 768 pixels", "ports": {"hdmi": 1, "usb": 0}, "speakers": {"left": "5 watt", "right": "5 watt"}}'
);
INSERT INTO `e_store`.`products`(
`name` ,
`brand_id` ,
`category_id` ,
`attributes`
)
VALUES(
'Proton' ,
'1' ,
'1' ,
'{"screen": "20 inch", "resolution": "1280 x 720 pixels", "ports": {"hdmi": 0, "usb": 0}, "speakers": {"left": "5 watt", "right": "5 watt"}}'
);
INSERT INTO `e_store`.`products`(
`name` ,
`brand_id` ,
`category_id` ,
`attributes`
)
VALUES(
'Desire' ,
'2' ,
'2' ,
JSON_OBJECT(
"network" ,
JSON_ARRAY("GSM" , "CDMA" , "HSPA" , "EVDO") ,
"body" ,
"5.11 x 2.59 x 0.46 inches" ,
"weight" ,
"143 grams" ,
"sim" ,
"Micro-SIM" ,
"display" ,
"4.5 inches" ,
"resolution" ,
"720 x 1280 pixels" ,
"os" ,
"Android Jellybean v4.3"
)
);
INSERT INTO `e_store`.`products`(
`name` ,
`brand_id` ,
`category_id` ,
`attributes`
)
VALUES(
'Passion' ,
'2' ,
'2' ,
JSON_OBJECT(
"network" ,
JSON_ARRAY("GSM" , "CDMA" , "HSPA") ,
"body" ,
"6.11 x 3.59 x 0.46 inches" ,
"weight" ,
"145 grams" ,
"sim" ,
"Micro-SIM" ,
"display" ,
"4.5 inches" ,
"resolution" ,
"720 x 1280 pixels" ,
"os" ,
"Android Jellybean v4.3"
)
);
INSERT INTO `e_store`.`products`(
`name` ,
`brand_id` ,
`category_id` ,
`attributes`
)
VALUES(
'Emotion' ,
'2' ,
'2' ,
JSON_OBJECT(
"network" ,
JSON_ARRAY("GSM" , "CDMA" , "EVDO") ,
"body" ,
"5.50 x 2.50 x 0.50 inches" ,
"weight" ,
"125 grams" ,
"sim" ,
"Micro-SIM" ,
"display" ,
"5.00 inches" ,
"resolution" ,
"720 x 1280 pixels" ,
"os" ,
"Android KitKat v4.3"
)
);
INSERT INTO `e_store`.`products`(
`name` ,
`brand_id` ,
`category_id` ,
`attributes`
)
VALUES(
'Sensation' ,
'2' ,
'2' ,
JSON_OBJECT(
"network" ,
JSON_ARRAY("GSM" , "HSPA" , "EVDO") ,
"body" ,
"4.00 x 2.00 x 0.75 inches" ,
"weight" ,
"150 grams" ,
"sim" ,
"Micro-SIM" ,
"display" ,
"3.5 inches" ,
"resolution" ,
"720 x 1280 pixels" ,
"os" ,
"Android Lollipop v4.3"
)
);
INSERT INTO `e_store`.`products`(
`name` ,
`brand_id` ,
`category_id` ,
`attributes`
)
VALUES(
'Joy' ,
'2' ,
'2' ,
JSON_OBJECT(
"network" ,
JSON_ARRAY("CDMA" , "HSPA" , "EVDO") ,
"body" ,
"7.00 x 3.50 x 0.25 inches" ,
"weight" ,
"250 grams" ,
"sim" ,
"Micro-SIM" ,
"display" ,
"6.5 inches" ,
"resolution" ,
"1920 x 1080 pixels" ,
"os" ,
"Android Marshmallow v4.3"
)
);
INSERT INTO `e_store`.`products`(
`name` ,
`brand_id` ,
`category_id` ,
`attributes`
)
VALUES(
'Explorer' ,
'3' ,
'3' ,
JSON_MERGE_PRESERVE(
'{"sensor_type": "CMOS"}' ,
'{"processor": "Digic DV III"}' ,
'{"scanning_system": "progressive"}' ,
'{"mount_type": "PL"}' ,
'{"monitor_type": "LCD"}'
)
);
INSERT INTO `e_store`.`products`(
`name` ,
`brand_id` ,
`category_id` ,
`attributes`
)
VALUES(
'Runner' ,
'3' ,
'3' ,
JSON_MERGE_PRESERVE(
JSON_OBJECT("sensor_type" , "CMOS") ,
JSON_OBJECT("processor" , "Digic DV II") ,
JSON_OBJECT("scanning_system" , "progressive") ,
JSON_OBJECT("mount_type" , "PL") ,
JSON_OBJECT("monitor_type" , "LED")
)
);
INSERT INTO `e_store`.`products`(
`name` ,
`brand_id` ,
`category_id` ,
`attributes`
)
VALUES(
'Traveler' ,
'3' ,
'3' ,
JSON_MERGE_PRESERVE(
JSON_OBJECT("sensor_type" , "CMOS") ,
'{"processor": "Digic DV II"}' ,
'{"scanning_system": "progressive"}' ,
'{"mount_type": "PL"}' ,
'{"monitor_type": "LCD"}'
)
);
INSERT INTO `e_store`.`products`(
`name` ,
`brand_id` ,
`category_id` ,
`attributes`
)
VALUES(
'Walker' ,
'3' ,
'3' ,
JSON_MERGE_PRESERVE(
'{"sensor_type": "CMOS"}' ,
'{"processor": "Digic DV I"}' ,
'{"scanning_system": "progressive"}' ,
'{"mount_type": "PL"}' ,
'{"monitor_type": "LED"}'
)
);
INSERT INTO `e_store`.`products`(
`name` ,
`brand_id` ,
`category_id` ,
`attributes`
)
VALUES(
'Jumper' ,
'3' ,
'3' ,
JSON_MERGE_PRESERVE(
'{"sensor_type": "CMOS"}' ,
'{"processor": "Digic DV I"}' ,
'{"scanning_system": "progressive"}' ,
'{"mount_type": "PL"}' ,
'{"monitor_type": "LCD"}'
)
);
# insert null
INSERT INTO `e_store`.`products`(
`name` ,
`brand_id` ,
`category_id` ,
`attributes`
)
VALUES(
'I am nu;;' ,
'3' ,
'3' ,
NULL
);
Raw
mysql-bin-changelog.000050.bin
�binq��a�!2w{5.7.36-log8
_
**45��"q��a#�!2����P]��a"�!2A���]��a�!2��1$@std-- e_storee_storeCREATE DATABASE IF NOT EXISTS `e_store`
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ciɦ��]��a"�!2A�鬪�]��a�!2��1 $@std-- e_storemy_databaseCREATE TABLE `e_store`.`brands`(
`id` INT UNSIGNED NOT NULL auto_increment ,
`name` VARCHAR(250) NOT NULL ,
PRIMARY KEY(`id`)
)��$$]��a"�!2A����k]��a�!2��1 $@std-- e_storemy_databaseCREATE TABLE `e_store`.`categories`(
`id` INT UNSIGNED NOT NULL auto_increment ,
`name` VARCHAR(250) NOT NULL ,
PRIMARY KEY(`id`)
)�ܲ7^��a"�!2AZ䤇^��a�!2Oa1 @std--my_databaseBEGIN���^��a�!27��e_storebrands��g�^��a�!21����Samsung �ŕ^��a�!2�-&p��^��a"�!2A)��^��a�!2Ox1 @std--my_databaseBEGIN��8^��a�!27��e_storebrands��i^��a�!2/����NokiaNX��^��a�!2�.&/�<^��a"�!2A>\�"_^��a�!2O�1 @std--my_databaseBEGINu���^��a�!27��e_storebrands���ͤ^��a�!2/����Canon�.!f^��a�!2/&�N)�^��a"�!2AS��ʙ^��a�!2O�1 @std--my_databaseBEGINK�)^��a�!2;��e_store
categories�uQo^��a�!24���
Televisionh\�^��a�!200&8�g^��a"�!2AqDu,v^��a�!2O�1 @std--my_databaseBEGINףG^��a�!2;��e_store
categories����^��a�!261 ��� Mobile Phone���^��a�!2P 1&
R)�^��a"�!2A�  ڞj3^��a�!2O� 1 @std--my_databaseBEGIN��ݕ^��a�!2;
�e_store
categories��^��a�!20K
���CameraV@I^��a�!2j
2&���^��a"�!2A�

6k��^��a�!2�V
1 $@std-- e_storemy_databaseCREATE TABLE `e_store`.`products`(
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(250) NOT NULL ,
`brand_id` INT UNSIGNED NOT NULL ,
`category_id` INT UNSIGNED NOT NULL ,
`attributes` JSON NOT NULL ,
PRIMARY KEY(`id`) ,
INDEX `CATEGORY_ID`(`category_id` ASC) ,
INDEX `BRAND_ID`(`brand_id` ASC) ,
CONSTRAINT `brand_id` FOREIGN KEY(`brand_id`) REFERENCES `e_store`.`brands`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE ,
CONSTRAINT `category_id` FOREIGN KEY(`category_id`) REFERENCES `e_store`.`categories`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE
)w�ɍ^��a"�!2A�

���^��a�!2O�
1 @std--my_databaseBEGIN��#�^��a�!2=#�e_storeproducts����z<^��a�!2�����Prime�� %+3
= V^ �portsscreenspeakersresolutionusbhdmi50 inch+  #leftright10 watt10 watt2048 x 1152 pixels���I^��a�!24&ٮ��^��a"�!2A[ ��>^��a�!2O�1 @std--my_databaseBEGIN=>��^��a�!2=��e_storeproducts����X�^��a�!2�����Octoview�� %+3
= V^ �portsscreenspeakersresolutionusbhdmi40 inch+  #leftright10 watt10 watt1920 x 1080 pixels�=��^��a�!2�5&�[�^��a"�!2A"
(R͖^��a�!2Oq1 @std--my_databaseBEGINi�a�^��a�!2=��e_storeproducts����� ^��a�!2�����Dreamer�� %+3
= V^ �portsscreenspeakersresolutionusbhdmi30 inch+  #leftright10 watt10 watt1600 x 900 pixles ,n^��a�!2�6&{U�2^��a"�!2A�
��Qs^��a�!2O61 @std--my_databaseBEGIN:;Mh^��a�!2=s�e_storeproducts����^��a�!2�I���Bravia�� %+3
= V^ �portsscreenspeakersresolutionusbhdmi25 inch)  "leftright5 watt5 watt1366 x 768 pixels�u�2^��a�!2h7&���W^��a"�!2A����^��a�!2O�1 @std--my_databaseBEGINc7%'^��a�!2=5�e_storeproducts��
g�9^��a�!2� ���Proton�� %+3
= V^ �portsscreenspeakersresolutionusbhdmi20 inch)  "leftright5 watt5 watt1280 x 720 pixelsD5��^��a�!2*8&SIN^��a"�!2Akn{h�^��a�!2O�1 @std--my_databaseBEGINC���^��a�!2=��e_storeproducts���KN�^��a�!2���Desire��57:>DKR
\ s } � �� �ossimbodyweightdisplaynetworkresolutionAndroid Jellybean v4.3 Micro-SIM5.11 x 2.59 x 0.46 inches 143 grams
4.5 inches#    GSMCDMAHSPAEVDO720 x 1280 pixelsl�(Z^��a�!249&�6,�^��a"�!2Auʃ��^��a�!2O�1 @std--my_databaseBEGIN�OC^��a�!2=�e_storeproducts��Wui�^��a�!2���Passion��57:>DKR
\ s } � �� �ossimbodyweightdisplaynetworkresolutionAndroid Jellybean v4.3 Micro-SIM6.11 x 3.59 x 0.46 inches 145 grams
4.5 inches
 GSMCDMAHSPA720 x 1280 pixels���^��a�!27:&���G^��a"�!2Ax<y�^��a�!2O�1 @std--my_databaseBEGIN_*��^��a�!2=�e_storeproducts��KAƅ^��a�!2���Emotion��57:>DKR
\ p z � �� �ossimbodyweightdisplaynetworkresolutionAndroid KitKat v4.3 Micro-SIM5.50 x 2.50 x 0.50 inches 125 grams 5.00 inches
 GSMCDMAEVDO720 x 1280 pixels��DZ^��a�!28;&�E}^��a"�!2Ay�Y͢^��a�!2O�1 @std--my_databaseBEGIN ?j^��a�!2=�e_storeproducts��rI��^��a�!2��� Sensation��57:>DKR
\ r | � �� �ossimbodyweightdisplaynetworkresolutionAndroid Lollipop v4.3 Micro-SIM4.00 x 2.00 x 0.75 inches 150 grams
3.5 inches
 GSMHSPAEVDO720 x 1280 pixelsY~�X^��a�!2<<&]�p^��a"�!2A}�dU"^��a�!2O�1 @std--my_databaseBEGINt.$^��a�!2= �e_storeproducts��]�@J^��a�!2 ���
Joy��57:>DKR
\ u  � �� �ossimbodyweightdisplaynetworkresolutionAndroid Marshmallow v4.3 Micro-SIM7.00 x 3.50 x 0.25 inches 250 grams
6.5 inches
 CDMAHSPAEVDO1920 x 1080 pixels|pu�^��a�!2? =&�=$C^��a"�!2A� �t.�^��a�!2O� 1 @std--my_databaseBEGINA�R�^��a�!2= !�e_storeproducts���\��^��a�!2��!��� Explorer��' 0
: E Q ` m p u yprocessormount_typesensor_typemonitor_typescanning_system Digic DV IIIPLCMOSLCD progressivel⺆^��a�!2�!>&��E�^��a"�!2A0"�:B^��a�!2O"1 @std--my_databaseBEGIN�ND�^��a�!2=�"�e_storeproducts��ج�|^��a�!2�}#��� Runner��' 0
: E Q ` l o t xprocessormount_typesensor_typemonitor_typescanning_system Digic DV IIPLCMOSLED progressiveޏ�^��a�!2�#?&�低^��a"�!2A�#$��]^��a�!2O,$1 @std--my_databaseBEGIN�w�w^��a�!2=i$�e_storeproducts��َ�<^��a�!2�,%���
Traveler��' 0
: E Q ` l o t xprocessormount_typesensor_typemonitor_typescanning_system Digic DV IIPLCMOSLCD progressiveD o�^��a�!2K%@&�6��^��a"�!2A�%�&6�^��a�!2O�%1 @std--my_databaseBEGINNF�^��a�!2=&�e_storeproducts��'�=�^��a�!2��&���Walker��' 0
: E Q ` k n s wprocessormount_typesensor_typemonitor_typescanning_system
Digic DV IPLCMOSLED progressive�
�^��a�!2�&A&�`��^��a"�!2A8'��B�^��a�!2O�'1 @std--my_databaseBEGIN��<^��a�!2=�'�e_storeproducts����?v^��a�!2��(���Jumper��' 0
: E Q ` k n s wprocessormount_typesensor_typemonitor_typescanning_system
Digic DV IPLCMOSLCD progressiveT�^��a�!2�(B&u/c����a"�!2A�(V�����a�!2L0)" @std-- mysqlBEGIN���@���a�!2��)" @std-- mysqlINSERT INTO mysql.rds_heartbeat2(id, value) values (1,1641740698866) ON DUPLICATE KEY UPDATE value = 1641740698866��ǚ��a�!2*�&4�g~���a�!29A*mysql-bin-changelog.0000514�$�
Raw
mysql-bin-changelog.000052.bin
�bin���a�!2w{5.7.36-log8
_
**4 �}2���a#�!2���4p����a"�!2A���}����a�!2�m1$@std-- e_storee_storeALTER TABLE `products` CHANGE `attributes` `attributes` JSON
NULL�H:;(��a"�!2A�Y�'F(��a�!2K�1@std--e_storeBEGIN� = (��a�!2=6�e_storeproducts�� R^u(��a�!2;q��� I am nu;;�d�(��a�!2��'�ԭ
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment