GORM,MySQL,PG

package main import ( "GORM/orm/dao" "fmt" _ "github.com/go-sql-driver/mysql" "github.com/jinzhu/gorm" _ "github.com/jinzhu/gorm/dialects/postgres" "time" ) type Gist struct { Id int64 Url string Snip string Kword string Md5 string language string } func main() { const ( mysqladdr = "root:[email protected](127.0.0.1:3306)/goods?charset=utf8mb4&parseTime=true&loc=Local" postgaddr = `host=5432 user=postgres dbname=goods sslmode=disable password=123456` ) //psqlInfo := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", "127.0.0.1", 5432, "postgres", "123456", "goods") db, err := gorm.Open("mysql", mysqladdr) db.LogMode(true) if err != nil { fmt. [Read More]

postgres01

pg常用命令 \password:设置当前登录用户的密码 \h:查看SQL命令的解释,比如\h select。 \?:查看psql命令列表。 \l:列出所有数据库。 \c [database_name]:连接其他数据库。 \d:列出当前数据库的所有表格。 \d [table_name]:列出某一张表格的结构。 \du:列出所有用户。 \e:打开文本编辑器。 \conninfo:列出当前数据库和连接的信息。 \password [user]: 修改用户密码 \q:退出 # Connect to PostgreSQL, using a superuser named 'postgres' psql -U postgres -h localhost pg 测试数据生成 select id from generate_series(1,10) t(id); #随机数 select (random()*100)::int from generate_series(1,10); #生成随机字符串 select md5(random()::text) from generate_series(1,10); #随机汉字 create or replace function gen_hanzi(int) returns text as $$ declare res text; begin if $1 >=1 then select string_agg(chr(19968+(random()*20901)::int), '') into res from generate_series(1,$1); return res; end if; return null; end; $$ language plpgsql strict; select gen_hanzi(10) from generate_series #随机数组 create or replace function gen_rand_arr(int,int) returns int[] as $$ select array_agg((random()*$1)::int) from generate_series(1,$2); $$ language sql strict; # 随机身份证 create or replace function gen_id( a date, b date ) returns text as $$ select lpad((random()*99)::int::text, 2, '0') || lpad((random()*99)::int::text, 2, '0') || lpad((random()*99)::int::text, 2, '0') || to_char(a + (random()*(b-a))::int, 'yyyymmdd') || lpad((random()*99)::int::text, 2, '0') || random()::int || (case when random()*10 >9 then 'X' else (random()*9)::int::text end ) ; $$ language sql strict; select gen_id('1900-01-01', '2017-10-16') from generate_series(1,10); gin 倒排索引 CREATE INDEX "name " ON "tablename" USING gin(to_tsvector('jiebacfg', "columnname")); CREATE INDEX rumidx ON rum_test USING rum (c1 rum_tsvector_ops); pg 查询 4#全文检索 select * from tbl where tsvector_col @@ 'postgres & china | digoal:A' order by ts_rank(tsvector_col, 'postgres & china | digoal:A') limit xx; 5正则查询。(可以使用pg_trgm和gin索引) select * from tbl where col ~ '^a[0-9]{1,5}\ +digoal$'; 6、相似查询。(可以使用pg_trgm和gin索引) select * from tbl order by similarity(col, 'postgre') desc limit 10; 7ADHOC查询,任意字段组合查询。(通过bloom index, multi-index bitmap scan, gin-index bitmap scan 等索引都可以实现) select * from tbl where a=? [Read More]

postgres02

pg upsert .5+ UPSERT用法举例 创建一张测试表,其中一个字段为唯一键或者主键。 create table test(id int primary key, info text, crt_time timestamp); 1. 不存在则插入,存在则更新 test03=# insert into test values (1,'test',now()) on conflict (id) do update set info=excluded.info,crt_time=excluded.crt_time; INSERT 0 1 test03=# select * from test; id | info | crt_time ----+------+---------------------------- 1 | test | 2017-04-24 15:27:25.393948 (1 row) test03=# insert into test values (1,'hello ,now()) on conflict (id) do update set info=excluded.info,crt_time=excluded.crt_time; INSERT 0 1 test03=# select * from test; id | info | crt_time ----+--------------+---------------------------- 1 | hello | 2017-04-24 15:27:39. [Read More]