mysql postgreSQL utils (python)

tips.sql

## Create table
id int(11) NOT NULL AUTO_INCREMENT

elements integer[4]

title TEXT CHARACTER SET utf8 NOT NULL
source VARCHAR(100) CHARACTER SET utf8 NOT NULL
doc_id  INT REFERENCES documents ON DELETE CASCADE

review set('positive', 'negative', 'neutral') COMMENT '",".join(data) when query'

created_at DATETIME DEFAULT CURRENT_TIMESTAMP
updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP

PRIMARY KEY (id)
UNIQUE KEY `source_UNIQUE` (source)

# DATETIME range(1000-9999)
# TIMESTAMP range(1970-2038)

## Query
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_SCHEMA = 'public' AND TABLE_NAME = 'documents' 
# return Object
->
# return String
->>
# select n-th in array
SELECT 'array'->0->'key' FROM item
# get result as json
conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

## Others
# explain query
EXPLAIN QUERY...
# turn on timing in psql
\timing 
# add result to array
array_agg()
# unnest array
unnest()
SELECT id, unnest(string_to_array(elements, ',')) AS elem FROM myTable

# config
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
default-storage-engine = innodb

# export import
mysqldump -u [username] -p [database name] > [database name].sql
mysql -u [username] -p newdatabase < [database name].sql