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

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 的时候，需要在查询出各分表数据之后，在中间件组件中自行实现对数据的处理

+----+-----------+
| 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..的解决方法：

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语句查询表结构:

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，所以对于结构太复杂的表可能会发生截断，显示不完整。

解决办法:将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.

表结构

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);

执行结果

-- 定义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);

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了，这一定有他们的道理，我想也没什么可以多说的了。

#创建表
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;

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