让MySQL查询区分大小写

由于MySQL默认是不区分大小写限制的,有时候业务需求需要加以区分,可以这么办。

要区分大小写SQL加binary说明比如select * from some_table where binary str='abc'select * from some_table where binary str='Abc'的结果是不一样的

嫌每次SQL中加标识麻烦的话,可以在建表的时候加以说明

create table some_table(
     str char(20) binary  
)

PostgreSQL & PostGIS LBS检索

PostGIS安装,点这里

高性能ElasticSearch LBS检索解决方案点击这里
LBS 通用检索解决方案点击这里

1.建表

-- 只给出gis字段
select AddGeometryColumn('表名','要添加的字段名',4326,'geometry',2);
-- 4326对应的是WGS84坐标系 中国使用的是该类型

有关WGS84坐标系介绍及深入了解请点这里……

2.插入数据

insert into table_name
    (name,geometry_column)
values
    ('用户1', ST_GeometryFromText('POINT(longitude latitude)',4326))

3.附近的人搜索(在此之前先给geometry_column添加GiST索引)

select
    *,
    ST_Distance_Sphere(
                      geometry_column,
                      ST_GeometryFromText('POINT(longitude latitude)',
                      4326
                      ) distance
from
    table_name
order by
    geometry_column <-> ST_GeometryFromText('POINT(longitude latitude)',4326)
LIMIT 10 offset 0

结束

Postgis安装指南

1.下载
http://www.posgtis.net/
建议下载源码,自行编译安装

2.安装依赖

apt-get install libgeos++-dev  libproj-dev libjson0-dev libjson-c-dev -y

其他依赖:gdal,请在www.gdal.org下载
编译gdal

./configure --with-pg=/usr/local/postgresql/bin/pg_config --with-geos
make && make install

*gdal编译完成后会提示支持哪些功能扩展,建议能开启的都开启

3.安装Postgis
安装过程中提示缺少的lib*自行安装

./configure --with-pgconfig=/usr/local/postgresql/bin/pg_config && make && make install

4.登录Psql,在指定数据库中开启postgis扩展(不要问为啥已经安装了还要开启)

postgres#: create extension postgis;
CREATE EXTENSION
postgres#: create extension postgis_topology;
CREATE EXTENSION

安装完成。

PostgreSQL更改字段顺序

要注意这并不是一个好主意,如非必要,请不要尝试去修改一个表的字段顺序,有该需求请尝试去重建表。

1.通过pg_class查找[表,索引,视图等的名字],[表在磁盘上的文件的名字]
SELECT relname, relfilenode FROM pg_class WHERE relname='order_change_table'

查询结果为:order_change_table | 12666

2.通过pg_attribute查找[此列/字段所属的表],[字段名字],[字段数目]
SELECT attrelid, attname, attnum FROM pg_attribute WHERE attrelid=12666

查询结果为:12666 | id | 1 12666 | name | 2 12666 | password | 3 12666 | new_field | 4

3.更新pg_attribute的[attnum]字段(将要移动的字段先更新成数据库里面没有的值,再按顺序更新)。

UPDATE pg_attribute SET attnum=7 WHERE attname='new_field' AND attrelid=12666;
UPDATE pg_attribute SET attnum=6 WHERE attname='name' AND attrelid=12666;
UPDATE pg_attribute SET attnum=5 WHERE attname='password' AND attrelid=12666;
UPDATE pg_attribute SET attnum=2 WHERE attname='new_field' AND attrelid=12666;
UPDATE pg_attribute SET attnum=3 WHERE attname='name' AND attrelid=12666;
UPDATE pg_attribute SET attnum=4 WHERE attname='password' AND attrelid=12666;

4.再检索表,字段就已经改好顺序了。(缺点:一旦改错表就崩溃,事先一定要备份好。优点:直达根处)
SELECT * FROM order_change_table

阿里云使用Atlas从外网访问MySQL(RDS),Or, 自动分表

先纠正误区:
在ECS上使用Atals中转访问RDS不会影响效率,不要认为使用Atals后连接数据库会慢,连接数会疯狂占用,事实情况是刚好相反。

Atlas不处理主从数据库数据同步,只是个负载均衡的连接池工具,主从同步请使用其他工具另行实现,本文只为了外网访问RDS(流量免费),实现连接池,有效利用RDS连接数。

Atlas是由 Qihoo 360公司Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它在MySQL官方推出的MySQL-Proxy 0.8.2版本的基础上,修改了大量bug,添加了很多功能特性。目前该项目在360公司内部得到了广泛应用,很多MySQL业务已经接入了Atlas平台,每天承载的读写请求数达几十亿条。

主要功能:
读写分离 从库负载均衡 IP过滤 自动分表 DBA可平滑上下线DB 自动摘除宕机的DB

继续阅读阿里云使用Atlas从外网访问MySQL(RDS),Or, 自动分表

PostgresQL启动脚本

Debina 7.5 / Ubuntu 14.04 Lts 测试通过

建议使用systemd进行管理,不要再使用init脚本了

#名字自己取
vim /etc/init.d/pgsql

写入如下内容:

#! /bin/sh

# chkconfig: 2345 98 02
# description: PostgreSQL RDBMS

# 安装路径
prefix=/usr/local/postgres
# 数据路径
PGDATA="/data/pgsql"
# 启动用户
PGUSER=pgsql
# log路径 一般不用改
PGLOG="$PGDATA/pgsql.log"

#OOM_SCORE_ADJ=-1000
#OOM_ADJ=-17


PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

DAEMON="$prefix/bin/postmaster"

PGCTL="$prefix/bin/pg_ctl"

set -e

test -x $DAEMON ||
{
    echo "$DAEMON not found"
    if [ "$1" = "stop" ]
    then exit 0
    else exit 5
    fi
}


case $1 in
  start)
    echo -n "Starting PostgreSQL: "
    test x"$OOM_SCORE_ADJ" != x && echo "$OOM_SCORE_ADJ" > /proc/self/oom_score_adj
    test x"$OOM_ADJ" != x && echo "$OOM_ADJ" > /proc/self/oom_adj
    su - $PGUSER -c "$DAEMON -D '$PGDATA' &" >>$PGLOG 2>&1
    echo "ok"
    ;;
  stop)
    echo -n "Stopping PostgreSQL: "
    su - $PGUSER -c "$PGCTL stop -D '$PGDATA' -s -m fast"
    echo "ok"
    ;;
  restart)
    echo -n "Restarting PostgreSQL: "
    su - $PGUSER -c "$PGCTL stop -D '$PGDATA' -s -m fast -w"
    test x"$OOM_SCORE_ADJ" != x && echo "$OOM_SCORE_ADJ" > /proc/self/oom_score_adj
    test x"$OOM_ADJ" != x && echo "$OOM_ADJ" > /proc/self/oom_adj
    su - $PGUSER -c "$DAEMON -D '$PGDATA' &" >>$PGLOG 2>&1
    echo "ok"
    ;;
  reload)
        echo -n "Reload PostgreSQL: "
        su - $PGUSER -c "$PGCTL reload -D '$PGDATA' -s"
        echo "ok"
        ;;
  status)
    su - $PGUSER -c "$PGCTL status -D '$PGDATA'"
    ;;
  *)
    # Print help
    echo "Usage: $0 {start|stop|restart|reload|status}" 1>&2
    exit 1
    ;;
esac

exit 0
# 设置自启动
update-rc.d -f pgsql defaults

完成

编译安装PostgreSQL

以下操作以Ubuntu 14.04 Lts,PostgrSQL 9.3.4为准

第一步:获取postgresql安装文件 安装依赖包

wget http://ftp.postgresql.org/pub/source/v9.3.4/postgresql-9.3.4.tar.bz2
tar xvf postgresql-9.3.4.tar.bz2

aptitude install libreadline-dev -y

第二步:指定编译参数并安装

cd postgresql-9.3.4
./configure --prefix=/usr/local/pgsql
make && make install

第三步:创建相关用户及目录

groupadd pgsql
useradd pgsql -g pgsql -s /bin/bash

mkdir -p /opt/pgsqldata

chown -R pgsql:pgsql /opt/pgsqldata
chown -R pgsql:pgsql /usr/local/pgsql

第四步:初始化数据库并启动

su pgsql

/usr/local/pgsql/bin/initdb -D /opt/pgsqldata

/usr/local/pgsql/bin/pg_ctl -D /opt/pgsqldata -l /opt/pgsqldata/pgsql.log start

或者进行如下操作:

su - pgsql -c "/usr/local/pgsql/bin/pg_ctl -D /opt/pgsqldata > /opt/pgsqldata/pg.log 2>&1 &"

完成