按照网上的教程安装PostgreSQL，按照完毕后，创建用户并设置了密码，但是无论是通过本地、还是远程客户端，都无需密码或者密码错误都可以正常登陆。 这就很有意思了。 翻看了 PostgreSQL的客户端授权配置文件pg_hba.conf，自己设置错误了参数 原来设置的最后一列method是trust，表示不验证密码登录
|
1 2 |
# IPv4 local connections: host all all 0.0.0.0/0 trust |
改为pas
Read more获取单表上所有索引的建表语句
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` ', 'ADD ', IF(NON_UNIQUE = 1, CASE UPPER(INDEX_TYPE) WHEN 'FULLTEXT' THEN 'FULLTEXT INDEX' WHEN 'SPATIAL' THEN 'SPATIAL INDEX' ELSE CONCAT('INDEX `', INDEX_NAME, '` USING ', INDEX_TYPE) END, IF(UPPER(INDEX_NAME) = 'PRIMARY', CONCAT('PRIMARY KEY USING ', INDEX_TYPE), CONCAT('UNIQUE INDEX `', INDEX_NAME, '` USING ', INDEX_TYPE))), '(', GROUP_CONCAT(DISTINCT CONCAT('`', COLUMN_NAME, '`') ORDER BY SEQ_IN_INDEX ASC SEPARATOR ', '), ');') AS 'Show_Add_Indexes' FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = '库名' AND TABLE_NAME = '表明' GROUP BY TABLE_NAME , INDEX_NAME ORDER BY TABLE_NAME ASC , INDEX_NAME ASC; |
获取非主键索引的建表语句
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` ', 'ADD ', IF(NON_UNIQUE = 1, CASE UPPER(INDEX_TYPE) WHEN 'FULLTEXT' THEN 'FULLTEXT INDEX' WHEN 'SPATIAL' THEN 'SPATIAL INDEX' ELSE CONCAT('INDEX `', INDEX_NAME, '` USING ', INDEX_TYPE) END, IF(UPPER(INDEX_NAME) = 'PRIMARY', CONCAT('PRIMARY KEY USING ', INDEX_TYPE), CONCAT('UNIQUE INDEX `', INDEX_NAME, '` USING ', INDEX_TYPE))), '(', GROUP_CONCAT(DISTINCT CONCAT('`', COLUMN_NAME, '`') ORDER BY SEQ_IN_INDEX ASC SEPARATOR ', '), ');') AS 'Show_Add_Indexes' FROM information_schema.STATISTICS WHERE UPPER(INDEX_NAME) <> 'PRIMARY' AND TABLE_SCHEMA = '库名' AND TABLE_NAME = '表名' GROUP BY TABLE_NAME , INDEX_NAME ORDER BY TABLE_NAME ASC , INDEX_NAME ASC; |
获取主键索引的建表语句
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` ', 'ADD ', IF(NON_UNIQUE = 1, CASE UPPER(INDEX_TYPE) WHEN 'FULLTEXT' THEN 'FULLTEXT INDEX' WHEN 'SPATIAL' THEN 'SPATIAL INDEX' ELSE CONCAT('INDEX `', INDEX_NAME, '` USING ', INDEX_TYPE) END, IF(UPPER(INDEX_NAME) = 'PRIMARY', CONCAT('PRIMARY KEY USING ', INDEX_TYPE), CONCAT('UNIQUE INDEX `', INDEX_NAME, '` USING ', INDEX_TYPE))), '(', GROUP_CONCAT(DISTINCT CONCAT('`', COLUMN_NAME, '`') ORDER BY SEQ_IN_INDEX ASC SEPARATOR ', '), ');') AS 'Show_Add_Indexes' FROM information_schema.STATISTICS WHERE UPPER(INDEX_NAME) = 'PRIMARY' AND TABLE_SCHEMA = '库名' AND TABLE_NAME = '表名' GROUP BY TABLE_NAME , INDEX_NAME ORDER BY TABLE_NAME ASC , INDEX_NAME ASC; |
如果想获取整个库中所有表的索引创建语句，可以写存储过程循环执行就行了。 &nbs
Read more基于行复制（row-base replication，RBR）的情况下， binlog_row_image 参数决定了行的镜像（image）是如何写入到binlog日志，改参数从MySQL 5.6引入。 binlog_row_image 是动态参数，可以直接修改，无需重启MySQL实例。 在RBR 中，每个行的变化都会含有两个镜像，前镜像（before image）和后镜像（aft
Read more