1. 主键+业务虚拟键分表

这种方式适合查询比较单一的业务，最大的缺点就是业务对分库分表这层是有感知:

1. 查询返回的时候需要在接口层拼接业务的真实Id
2. 根据真实业务Id查询，或者更新数据的时候，需要拆分真实业务Id，路由到数据所在表，再根据数据库Id查询

虚拟场景

假设我们分了1024张表，具体表结构如下：

插入流程

1. 插入数据，必须带有userId
2. 根据userId计算出xId
xId = userId % 10000;
3. 根据xId定位数据所在表
tNum = xId % 1024; // 最简单的取模hash(具体策略由中间件决定)
4. 插入数据，返回realId
realId = id + xId;

单条查询流程

1. 根据realId查询, 获取单张表中的Id值
id = realId / 10000; // 整除
2. 获取虚拟键xId
xId = realId % 10000;
3. 根据xId定位数据所在表
tNum = xId % 1024; // 最简单的取模hash(具体策略由中间件决定)
4. 根据表和Id获取单条数据

批量查询流程

基本和单条查询一致，可优化的点：

第3步，根据xId进行分组，将查询同一张表的query放在一次sql的查询语句中

2. 主键/业务外键分表

这种策略对业务代码可以无侵略性, 为避免Id冲突， 使用外部Id生成器, 以下步骤全在中间件中执行：
(根据业务外键的处理同理，查询比较复杂的情况一般都是使用冗余表)

虚拟场景

假设我们分了1024张表，具体表结构如下：

插入流程

1. 从Id生成器获取Id
2. 根据Id进行hash (简单的可以使用取模), 获取数据应该插入的表名
tNum = id % 1024;
3. 插入数据表

单条查询流程

1. 根据Id进行hash (简单的可以使用取模), 获取数据所在表名
tNum = id % 1024;
2. 查询数据

批量查询流程

基本和单条查询一致，可优化的点：

第1步，根据Id进行hash后进行分组，将查询同一张表的query放在一次sql的查询语句中

数据的聚合

在批量查询的时候，需要 order by group by distinct 的时候，需要在查询出各分表数据之后，在中间件组件中自行实现对数据的处理

find_in_set

+----+-----------+
| id | extra_ids |
+----+-----------+
| 1  | 1,2,3     |
| 2  | 4,5,6,1,3 |
| 3  | 7,3,1,2,5 |
+----+-----------+

-- 看到有人是用like做的，虽然这两种都会全表扫描，这样用会比like好。
select * from test where find_in_set(5, extra_ids);

group_concat

+----+----------+
| id | group_id |
+----+----------+
| 1  | 1        |
| 2  | 1        |
| 3  | 2        |
+----+----------+

-- group_concat(id)
select group_concat(id order by id desc separator '|') as ids from test group by group_id;

+-----+----------+
| ids | group_id |
+-----+----------+
| 2|1 | 1        |
| 3   | 2        |
+-----+----------+

昨天在数据库中查看论坛用户的活动时间和访问时间的间隔时，出现ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in..的错误。记录一下。

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in..的解决方法：

mysql> select lastvisit, lastactivity from pre_common_member_status limit 1;
+------------+--------------+
| lastvisit  | lastactivity |
+------------+--------------+
| 1199200260 |   1198336989 |
+------------+--------------+
1 row in set (0.01 sec)

mysql> select lastvisit-lastactivity from pre_common_member_status limit 1;
+------------------------+
| lastvisit-lastactivity |
+------------------------+
|                 863271 |
+------------------------+
1 row in set (0.05 sec)

mysql> select abs(lastvisit-lastactivity) from pre_common_member_status limit 1;

+-----------------------------+
| abs(lastvisit-lastactivity) |
+-----------------------------+
|                      863271 |
+-----------------------------+
1 row in set (0.03 sec)

mysql> select lastactivity-lastvisit from pre_common_member_status limit 1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`discuz`.`pre_c
ommon_member_status`.`lastactivity` - `discuz`.`pre_common_member_status`.`las
tvisit`)'
mysql> select cast(lastactivity as signed)-cast(lastvisit as signed) from pre_co
mmon_member_status limit 1;
+--------------------------------------------------------+
| cast(lastactivity as signed)-cast(lastvisit as signed) |
+--------------------------------------------------------+
|                                                -863271 |
+--------------------------------------------------------+
1 row in set (0.02 sec)

mysql> select abs(cast(lastactivity as signed)-cast(lastvisit as signed)) from p
re_common_member_status limit 1;
+-------------------------------------------------------------+
| abs(cast(lastactivity as signed)-cast(lastvisit as signed)) |
+-------------------------------------------------------------+
|                                                      863271 |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

通过上面的查询我们可以发现，当两个时间戳相减为负数时才会出现ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in..这个错误，但在这个表中两个值中大小不是固定的，lastactivity有可能比lastvisit大，也有可能比lastvisit小。。所以这里可以用cast()来解决。

用select语句来代替show create table来简单显示表结构，此语句不包含创建表索引语句。

select语句查询表结构:

SET @db_name='aa';
SET @table_name='bb';
SELECT TABLE_NAME,
CONCAT('CREATE TABLE ','`',TABLE_NAME,'` (',GROUP_CONCAT(
"\r\n",
CONCAT(CONCAT('`',COLUMN_NAME,'`'),
' ',
COLUMN_TYPE,
' ',
IF(IS_NULLABLE='NO','NOT NULL',''),
 IF(COLUMN_TYPE='text','',IF(COLUMN_DEFAULT IS NULL AND IS_NULLABLE='NO','',CONCAT(' DEFAULT ',IF(COLUMN_DEFAULT IS NULL,'NULL',CONCAT('\'',COLUMN_DEFAULT,'\'')))))),
IF(EXTRA='','',' auto_increment')
),
CONCAT(",\r\n",'PRIMARY KEY (`',(SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME=@table_name AND COLUMN_KEY='PRI'),'`)',"\r\n",')'),
(SELECT CONCAT(' ENGINE=',ENGINE,' DEFAULT CHARSET=',SUBSTRING(TABLE_COLLATION,1,LOCATE('_',TABLE_COLLATION)-1)) FROM information_schema.TABLES WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME=@table_name)
) AS TABLE_SCHEMA
FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME=@table_name;

话说这个改改貌似可以用来注入了。。

不过这个还是有局限性的，group_concat长度一般默认为1024，所以对于结构太复杂的表可能会发生截断，显示不完整。

插入大量数据出现 2006 Lost connection to MySQL server during query或MySQL server has gone away。

解决办法:将max_allowed_packet值调大。

Like this:

mysql> SHOW GLOBAL VARIABLES LIKE '%packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.00 sec)

mysql> SET GLOBAL max_allowed_packet=10485760;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE '%packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 10485760 |
+--------------------+----------+
1 row in set (0.00 sec)
mysql>

That's all.

对于load data infile的用法，官方的手册写的已经很全面了，不准备多说，仅写个实例。：）

表结构

CREATE TABLE `domain` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `extension` varchar(20) NOT NULL,
  `status` tinyint(1) unsigned NOT NULL,
  `create_time` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
)

data.txt

内容分别为域名和状态。

kller.cn,0
aweika.com,1
type.so,1
qiyuuu.com,1
xiaosong.org,1
inote.cc,2
xxx.com.cn,1

sql语句

LOAD DATA INFILE 'D:\data.txt'
INTO TABLE `domain`
FIELDS TERMINATED BY ','
(@domain,`status`)
SET `create_time`=UNIX_TIMESTAMP(),
`name`=SUBSTRING(@domain,1,LOCATE('.', @domain)-1),
`extension`=SUBSTRING(@domain,LOCATE('.', @domain)+1);

执行结果

利用UTL_FILE导出数据

-- 定义fhandle文件句柄
fhandle utl_file.file_type;
-- 打开文件
fhandle := utl_file.fopen(location => './', filename => 'export_' || to_char(sysdate, 'yyMMddHH24mi') || '.txt', open_mode => 'w', max_linesize => 32767);
-- 写入一行数据
utl_file.put_line(file => fhandle, buffer => 'test str');
-- 关闭句柄
utl_file.fclose(file => fhandle);

储存时间，常用的有三个选择datetime、timestamp、int。昨夜同事问到了，于是今天就总结一下自己的理解。

1. 插入效率：datetime > timestamp > int
2. 读取效率：int > timestamp > datetime
3. 储存空间：datetime > timestamp = int

让我们来看一个应用场景：

看下这张图，第一我们需要设置系统的默认时区，第二我们也需要提供不同时区时间显示的需要。于是，我们分别使用datetime、timestamp、int字段类型来看下：

使用datetime

直接显示时间，这是个不错的选择，但是如果考虑到时区，很明显计算上的麻烦。

使用timestamp

OK,这个很好，可以根据系统的时区来自动输出时间，但是单个用户要定制自己的时区呢？再者你不怕麻烦，在程序里面实现了这个计算，服务器若是换个地方，改了下时区，你程序里面计算单个用户当地时间的代码怎么办（timestamp出来的时间会根据时区的变化而变化，在某些情况下是不错的选择，但在某些情况下，真的很鸡肋）。

使用int

从上面两个类型的缺点看来，貌似这个类型可以解决以上的问题，其实我们只要存格林时间的unix timestamp就好了，时区时间的计算上也很方便，读取的效率也不错。我觉得用这个储存的缺点呢，就是直接select的时候时间不能直观的显示出来。

看看其他开源程序是怎么做的

discuz, typecho, emlog等等等等，他们都选用int了，这一定有他们的道理，我想也没什么可以多说的了。

一般的sql插入语句，当要插入的长度大于4000的时候，数据库就会报错。
可以使用dbms_lob包来处理，先插入一个空的clob值，再将其取出，利用dbms_lob包将数据写入，再更新表。(blob同理)

#创建表
DROP TABLE "SCOTT"."pre_test";
CREATE TABLE "SCOTT"."pre_test" (
"id" NUMBER(8) NULL,
"message" CLOB NULL
);
insert into "pre_test" ("message") values (empty_clob());

待续...

2013-12-11 23:51 更新

由于时间较长，手上目前木有Oracle数据库可供测试，以下代码为伪代码，不能正常运行，但能说明流程：

select message into x;
dbms_lob.write(x, ...);
update "SCOTT"."pre_test" set message=x;

导入备份的sql文件

use mydb;
source /var/www/data.sql;

导入txt数据

LOAD DATA INFILE 'path/to/file.txt'
INTO TABLE `tablename`
FIELDS TERMINATED BY ',';

时间戳和标准时间的转换

#时间戳 to 标准时间
FROM_UNIXTIME(xxxxxxxxxx);
#标准时间 to 时间戳
UNIX_TIMESTAMP('YYYY-MM-DD HH:MM:SS'');

Error: Cannot load from mysql.proc. The table is probably corrupted

mysql_upgrade -uroot -p

这里要说的是忘记root密码之后怎么去改密码。

killall -TERM mysqld #关闭mysql进程
/usr/local/mysql/bin/mysqld_safe --skip-grant-tables  --skip-networking & #安全模式启动mysql
mysql #直接mysql命令进去

注意该模式下进去是没有grant权限的，我们只能直接对表进行操作，下面是sql语句

USE mysql;
UPDATE user set password=password('new password') WHERE user='root';
FLUSH PRIVILEGES; #刷新权限

但是遇到徐老师这样的草泥马，把表清空了，N多的字段啊，果断从本地mysql的表里面搞出sql语句，插进去：

USE mysql;
INSERT INTO `user` VALUES ('%', 'root', PASSWORD('your password'), 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0, 0);
INSERT INTO `user` VALUES ('localhost', 'root', PASSWORD('your password'), 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0, 0);
FLUSH PRIVILEGES; #刷新权限

一些注意点

-- || 连接字符串和字段
-- 所有数据区分大小写
-- IN 语句的范围内存在NULL对结果无影响
-- NOT IN 语句中范围内存在NULL，则不返回任何数据
-- NULL计算后的值还是NULL
-- 日期 - 数字 = 日期
-- 日期 - 日期 = 数字

一些函数

DECODE(a, 1, '男', 2, '女') -- 你懂的
NVL(a, 0) -- 字段为NULL的值
MONTHS_BETWEEN(a, b) -- a,b两个日期之间相差的月数
ADD_MONTHS(sysdate, 2) -- 在日期上加上指定的月数
NEXT_DAY(sysdate, '星期一') -- 下一个星期一
LAST_DAY(sysdate) -- 一个月的最后一天
WITH sql1 as (SELECT * FROM test) SELECT * FROM sql1 WHERE  -- with语句

select lpad('tech', 2) from dual -- te
select lpad('tech', 8, '0') from dual -- 0000tech

TRUNC(TO_DATE('24-Nov-1999 08:00 pm', 'dd-mon-yyyy hh:mi am')) -- '24-Nov-1999 12:00:00 am' 
TRUNC(TO_DATE('24-Nov-1999 08:37 pm', 'dd-mon-yyyy hh:mi am','hh')) -- '24-Nov-1999 08:00:00 am'

TRUNC(89.985, 2) -- 89.98
TRUNC(89.985) -- 89
TRUNC(89.985, -1) -- 80

select t.Name, case t.id when 1 then 'zhao' when 2 then 'qian' when 3 then 'sun' else 'li' end "fname" from T t

WMSYS.WM_CONCAT -- 行转列

一些常见的报错

未明确定义列

这一般只有在有子查询的sql语句中才会出现的错误，以为子查询的结果中有同名的列。如：

select to_char(dateline, 'yyyy-mm-dd') as dateline from test_table

这样的sql语句作为子句的时候，ORACLE会认为有两个同名的dateline列。解决办法就是将as后面的dateline改为其他的，如：fdateline