OceanBaseSQL用户基础手册专业资料.doc

OceanBase SQL顾客手册 本手册合用于OceanBase 0.4.1

编号
文档版本
修订章节
修订因素
修订日期
修订人
1
0.1

新建文档
/10/16
竹翁
2
0.2

添加0.4.1版本新特性
/1/13
竹翁
3
0.3

细节阐明；添加SELECT FOR UPDATE阐明；阐明事务中SELECTBUG陷阱
/1/21
竹翁
4
0.4

陷阱和缺陷添加对varchar(32)阐明；
陷阱和缺陷添加REPLACE返回affected_rows阐明；
附录中添加PS语句API示例程序
/1/23
竹翁
5
0.5
4.1,5.1
SQL语句大小写不敏感；双引号转义阐明；新增alter table语句
/3/14
竹翁
6
0.6
4.2
增长对返回类型描述
/3/22
方戟

目录
1. OceanBase介绍 4
2. 快速上手 4
步骤一 4
步骤二 5
步骤三 5
3. 使用API 6
3.1. C程序 6
3.1.1. 编译方法一： 8
3.1.2. 编译方法二： 8
3.2. Java程序 8
4. 陷阱和缺陷 11
4.1. 支持的语句 11
4.2. 数据类型 11
4.3. 浮点数 11
4.4. 函数 12
4.5. Join的限制 12
4.6. update和delete的限制 12
4.7. 事务中SELECT的BUG 12
4.8. REPLACE语句返回的affected_rows值 12
5. SQL语法参考 13
5.1. 数据定义语句 13
5.1.1. create table语句 13
5.1.2. drop table语句 14
5.1.3. alter table语句 14
5.2. 数据操作语句 15
5.2.1. select语句 15
5.2.2. replace语句 18
5.2.3. insert语句 18
5.2.4. update语句 18
5.2.5. delete语句 18
5.3. 事务相关语句 19
5.3.1. 开始事务 19
5.3.2. 结束事务 19
5.4. 变量相关语句 19
5.4.1. set语句 19
5.4.2. show variables语句 20
5.5. 预备执行语句 20
5.5.1. prepare语句 20
5.5.2. execute语句 20
5.5.3. deallocate语句 21
5.5.4. 一个例子 21
6. 常见问题 21
6.1. 用replace还是insert？ 21
6.2. 什么情况下需要使用预备执行语句？ 21
附录 22
Prepared Statement API示例 22
SELECT示例 22
REPLACE示例 25

1. OceanBase简介
RootServer/
UpdateServer（主）
RootServer/
UpdateServer（备）
Chunkserver/
MergeServer

OceanBase是一种由RootServer，UpdateServer，ChunkServer，MergeServer等角色构成分布式数据库系统。RootServer是管理服务器，用于管理UpdateServer、ChunkServer和MergeServer等。ChunkServer是OceanBase基线数据存储子系统，由多台机器构成，基线数据普通保存2~3副本并且保存在不同ChunkServer上。UpdateServer是OceanBase增量更新子系统，记录顾客修改，普通由一主一备或一主多备构成。MergeServer是OceanBase顾客接口子系统，接受顾客查询祈求，并从相应ChunkServer上获得基线数据以及从UpdateServer上获得更新增量，然后融合两者后返回顾客。
SQL顾客只需要和MergeServer交互，每一种MergeServer都可以视为一种SQL服务器。顾客连接任何一台MergeServer都可以对所有表格数据执行SQL语句。
2. 迅速上手
OceanBase完全兼容MySQL网络合同，SQL顾客可以使用mysql终端客户端、以及使用libmysqlclient任意程序连接OceanBase。只要你熟悉mysql，那么你就可以直接使用OceanBase SQL服务了。下面咱们以mysql终端客户端为例演示一下。
环节一
请询问你OceanBase DBA任意一台MergeServerIP地址和SQL服务端口（默认端标语是2828）。@TODO改用LD_PRELOAD=libobsql.so启动mysql方式
环节二
使用mysql终端连接MergeServer，如下图。

注意红色某些，如果你看到类似这样字样，阐明你已经成功登陆了OceanBase！像使用MySQL同样体验一下吧。
环节三
咱们下面可以创立一种表格，插入某些数据，然后查询出来。很简朴，是不是：）

3. 使用API
3.1. C程序
OceanBase为应用程序提供了libobsql函数库 当前0.4.1版本没有发布libobsql，顾客暂时可以用libmysql代替。
。这个库在二进制接口上与mysqllibmysqlclient库完全兼容。也就是说，一种程序（例如mysql，mysqltest等）之前用libmysqlclient编译好，使用PRELOAD技术无需重新编译就可以使用来连接OceanBase。应用程序可以使用与libmysqlclient提供API（mysql.h）完全相似接口直接访问OceanBase SQL服务。下面是一种简朴例子程序完整清单。

#include <stdlib.h>
#include <stdio.h>
#include <mysql/mysql.h> //是，你没看错，就是mysql.h

int main(int argc，char *argv[])
{
(void)argc;
(void)argv;
const char* HOST = "127.0.0.1";
int PORT = 2828;
// 1. 初始化libmysqlclient
if (0 != mysql_library_init(0，NULL，NULL))
{
fprintf(stderr，"could not init mysql library\n");
exit(1);
}

MYSQL my_;
ASSERT_TRUE(NULL != mysql_init(&my_))；// 此处用ASSERT省略错误检查
fprintf(stderr，"Connecting server %s:%d...\n"，HOST，PORT);
// 2. 连接OceanBase SQL服务
ASSERT_TRUE(NULL != mysql_real_connect(&my_，HOST，""，""，"test"，PORT，NULL，0));
// 3. 建表
int ret = 0;
ret = mysql_query(&my_，"drop table if exists ob_new_sql_test");
if (0 != ret)
{
fprintf(stderr，"%s\n"，mysql_error(&my_));
}
ASSERT_EQ(0，ret);
ret = mysql_query(&my_，"create table if not exists ob_new_sql_test "
"(c0 varchar(64)，c1 int primary key，c2 int，"
"c3 int，c4 int，c5 int，c6 int，"
"c7 int，c8 int，c9 int，c10 int，"
"c11 int，c12 int，c13 int，c14 int,"
"c15 int)");
if (0 != ret)
{
fprintf(stderr，"%s\n"，mysql_error(&my_));
}
ASSERT_EQ(0，ret);
// 4. 插入数据
ret = mysql_query(&my_，"insert into ob_new_sql_test (c0，c1，c2)values ('hello'，1，2)");
if (0 != ret)
{
fprintf(stderr，"%s\n"，mysql_error(&my_));
}
ASSERT_EQ(0，ret);
// 5. 查询数据
ASSERT_EQ(0，mysql_query(&my_，"select * from ob_new_sql_test"));
MYSQL_RES *res = mysql_use_result(&my_)；// 取成果集
ASSERT_TRUE(NULL != res);
unsigned int num_fields = mysql_num_fields(res)；// 成果列数
ASSERT_EQ(16U，num_fields);

unsigned long *lengths = NULL;
MYSQL_ROW row;
row = mysql_fetch_row(res);
ASSERT_TRUE(NULL != row);
lengths = mysql_fetch_lengths(res)；// row中每个值长度，0表达NULL
ASSERT_TRUE(NULL != lengths);
for (unsigned int j = 0；j < num_fields；++j)
{
if (0 != j)
{
fprintf(stdout，",");
}
fprintf(stdout，"%.*s"，row[j] ?static_cast<int>(lengths[j])
：static_cast<int>(sizeof("NULL")-1)，row[j] ?row[j] ："NULL");
}
fprintf(stdout，"\n");
row = mysql_fetch_row(res);
ASSERT_TRUE(NULL == row)； // no more rows
mysql_free_result(res);
// 6. 删表
ret = mysql_query(&my_，"drop table if exists ob_new_sql_test");
if (0 != ret)
{
fprintf(stderr，"%s\n"，mysql_error(&my_));
}
// 7. destroy
mysql_library_end();
return 0;
}
恭喜你，你已经完毕了一种从OceanBase中读写数据程序。关于API完整阐明，请参照MySQL C API文档 见
。
3.1.1. 编译办法一：
一方面，需要保证你开发机上已经安装了mysql应用开发包，在RHEL或者fedora上可以使用如下命令安装：@TODO obsql如何安装
yum install mysql-devel
然后，拷贝上面程序到一种文献ob_sql_example.cpp中，用如下命令编译：
g++ -o ob_sql_example ob_sql_example.cpp –lmysqlclient
程序编译好后，用下面命令运营：
LD_LIBRARY_PATH=<path_to_libobsql> LD_PRELOAD=libobsql.so ./ob_sql_example
3.1.2. 编译办法二：
你也可以在编译时候直接使用libobsql代替libmysqlclient，这样产生可执行程序无需使用LD_PRELOAD方式来执行。应用程序源码不需要任何修改，还是包括mysql.h和使用其中API。@TODO补充libobsql执行示例环境变量设立和头文献
g++ -o ob_sql_example ob_sql_example.cpp –lobsql
./ob_sql_example
3.2. Java程序
OceanBase提供了符合Java原则DataSource 见
，OBGroupDataSource是javax.sql.DataSource一种实现，Java应用程序可以使用OBGroupDataSource获得与OceanBase服务器交互连接。这个库在mysql-connector-java基本上，实现了OceanBase多集群间流量分派，多MergeServer间负载均衡，以及在单个MergeServer发生失效状况下自动进行切换容灾等功能。下面是一种简朴例子程序完整清单。@TODO Java PS例子
package com.alipay.oceanbase;

import java.io.*;
import java.util.*;

import java.sql.*;
import javax.sql.DataSource;
import com.alipay.oceanbase.OBGroupDataSource;

public class ObJDBCExample
{
public static void main(String[] argv)
{
try {
// init the data source
OBGroupDataSource ds = new OBGroupDataSource();
List<String> instanceList = new ArrayList<String>();
instanceList.add("10.232.36.185:45447")；// OceanBase RootServer
ds.setInstanceList(instanceList)；//推荐使用setConfigURL方式，见后
ds.setUserName("user");
ds.setPasswd("password");
ds.init();
// JDBC stuff
Connection conn = ds.getConnection();
Statement stmt = conn.createStatement();
stmt.executeUpdate("drop table if exists t1");
stmt.executeUpdate("create table t1 (c1 int primary key，c2 varchar)");
stmt.executeUpdate("insert into t1 values (1，'hello ob')，(2，'hello java')");
ResultSet rs = stmt.executeQuery("select * from t1");
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
System.out.println("column count=" + columnCount);
while (rs.next()){
System.out.println(rs.getInt("c1") + "|" + rs.getString("c2"));
}
System.out.println("finish");
}
catch (SQLException ex) {
System.out.println(ex);
}
catch (Exception ex){
System.out.println(ex);
}
}
}
Java程序也可以通过Spring等框架来使用OBGroupDataSource。例如使用Spring JdbcTemplate：
OBGroupDataSource ds = new OBGroupDataSource();
ds.setUserName("user");
ds.setPasswd("pass");
ds.setDbName("test");
ds.setConfigURL(":8080/diamond-server/config.co?dataId=xxx")；//这个URL由OceanBase服务方提供
ds.init();
JdbcTemplate jtp = new JdbcTemplate();
jtp.setDataSource(ds);
String sql = "select 1 from dual";
int ret = jtp.queryForInt(sql);

通过Bean使用Spring配备：
<bean id="ds" class="com.alipay.oceanbase.OBGroupDataSource" init-method="init">
<property name="userName" value="user" />
<property name="passwd" value="pass"/>
<property name="dbName" value="test" />
<property name="configURL" value=":8080/diamond-server/config.co?dataId=xxx" />
</bean>
OBDataSource库重要maven依赖关系如下（请在你pom.xml中添加）：
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
<dependency>
<groupId>com.taobao.tbdatasource</groupId>
<artifactId>tbdatasource</artifactId>
<version>2.0.3-SNAPSHOT</version>
</dependency>
<dependency>
<groupId>com.alipay.oceanbase</groupId>
<artifactId>obdatasource</artifactId>
<version>0.0.1-SNAPSHOT</version>
</dependency>
4. 陷阱和缺陷
4.1. 支持语句
OceanBase SQL语法在参照MySQL语法同步，尽量遵循SQL92原则，在必要时候还参照了Oracle。
SQL语句中核心字，以及表名、列名、函数名等都是大小写不敏感。表名和列名都转换为小写之后存入schema中。因此，虽然顾客建表时候列名是大写，查询时候获得列名也是小写。
遵循SQL92原则，字符串字面量要用单引号表达，见Error! Reference source not found.。而双引号扩住名字被解释为表名、列名或函数名，而不是字符串。双引号内可以浮现SQL保存核心字。
OceanBase没有database概念，可以理解为一种oceanbase集群只有一种database，因此顾客不需要也不能使用use database语句来指定database。
当前版本支持语句有create table，drop table，select，insert，replace，delete，update，set，show等，详细语法请见Error! Reference source not found.。权限控制等管理语句参照《OceanBase SQL管理员手册》。
4.2. 数据类型
OceanBase不支持time，date类型，只支持datetime和timestamp类型（这两者等价）。顾客可以使用datetime，timestamp类型字面量（literal，见Error! Reference source not found.）。
OceanBase暂不支持decimal类型，将在下一种版本提供。
OceanBase提供了两个特殊数据类型：Createtime类型列中记录本行数据第一次插入时时间，由系统自动维护，顾客不能直接修改；Modifytime类型列中记录本行数据近来一次被修改时间，由系统自动维护，顾客不能直接修改。这两个类型列也不能作为主键构成某些。
OceanBase没有字符集概念。Char，Varchar，Binary，Varbinary在OceanBase内部都存储为不解释内容二进制变长字符串，类似于Varbinary。这种类型比较使用是字节序。此外，Create table时定义varchar列最大长度也是不起作用。例如varchar(32)，事实上可以插入不不大于32字节串，不以为是错误。
在OceanBase内部，Tinyint，Smallint，Mediumint，Int，Integer，Bigint无论语义还是实现都是等价，存储为8字节有符号整型，即int64_t。
OceanBase支持float和double浮点数类型，分别表达4字节和8字节浮点数，其语义等同于IEEE 754浮点数。
OceanBase 返回给客户端数据类型当前只也许是如下几种，客户端在绑定变量时候需要特别注意。
Data Type
MySQL Data Type
int
MYSQL_TYPE_LONGLONG
float
MYSQL_TYPE_FLOAT
double
MYSQL_TYPE_DOUBLE
datetime
MYSQL_TYPE_DATETIME
string
MYSQL_TYPE_VAR_STRING
bool
MYSQL_TYPE_TINY
decimal
MYSQL_TYPE_NEWDECIMAL
4.3. 浮点数
在MySQL中，型如“1.2345”字面量是作为decimal类型解决，型如“1.2345e18”字面量才作为浮点数解决。而当前版本OceanBase中，两者都作为浮点数类型double解决。
在MySQL中，在做表达式运算时，两个整数类型相除，成果是decimal类型。而当前版本OceanBase中，两者相除成果为double类型。
4.4. 函数
OceanBase遵循SQL原则，不支持MySQL COUNT(DISTINCT expr，expr，[expr...])语法，即汇集函数中只能浮现一种value表达式，不支持各种（例如count(distinct c1，c2)）。
OceanBase当前支持系统函数非常有限，只有length、substr、cast、current_time、current_date、current_timestamp、trim、lower、upper、coalesce等。咱们会依照顾客需求优先级不断添加新函数。
4.5. Join限制
当前，Join不支持USING子句，见背面Join语法。并且，Join连接条件中必要至少有一种等值连接条件。例如，下面两个例子是支持：
select * from t1 inner join t2 on t1.c1 = t2.c1 and t1.c2 < 1000;
select * from t1，t2 where t1.c1 = t2.c1 and t1.c2 < 1000;
而下面语句是不支持：
select * from t1 inner join t2 on t1.c1 > t2.c1 and t1.c2 < 1000;
4.6. update和delete限制
当前，update和delete语句必要指定primary key所有列作为限制条件，并且只支持单行。换言之，update、delete操作必要指定单行主键。例如
create table t1 (c1 int，c2 int，c3 int，primary key(c1，c2));
delete from t1 where c1 = 1 and c2 = 2;
update set c3 = c1 + 1 where c1 = 1 and c2 = 2;
而下面语句都是不支持：
delete from t1;
delete from t1 where c1 = 1;
update set c3 = c1 + 1;
update set c3 = c1 + 1 where c1 > 1 and c2 > 2;
4.7. 事务中SELECTBUG
当前在事务中（start transaction或者begin启动事务）执行SELECT语句，是不能读取当当前事务中未提交数据。这是一种已知功能BUG。顾客如果有这个需求，可以暂时用SELECT … FOR UPDATE语句代替。
4.8. REPLACE语句返回affected_rows值
使用API执行REPLACE语句时，执行完毕后可以获得affected_rows值。但是，当前OB返回affected_rows值是错误。应用程序不能依赖于这个值来判断行与否已经存在，或者replace与否成功（成功状况下，replace返回affected_rows也许也为0）。
5. SQL语法参照
1.
2.
3.
4.
5.
1.
2.
3.
4.
5.
5.1. 数据定义语句
5.1.1. create table语句
CREATE TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]

create_definition:
    col_name column_definition
  | PRIMARY KEY (index_col_name,...)

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [[PRIMARY] KEY]

data_type:
TINYINT[(length)]
  | SMALLINT[(length)]
  | MEDIUMINT[(length)]
  | INT[(length)]
  | INTEGER[(length)]
  | BIGINT[(length)]
  | REAL[(length,decimals)]
  | DOUBLE[(length,decimals)]
  | FLOAT[(length,decimals)]
  | DECIMAL[(length[,decimals])]
  | NUMERIC[(length[,decimals])]
  | TIMESTAMP
  | DATETIME
  | CHAR[(length)]
  | VARCHAR(length)
  | BINARY[(length)]
  | VARBINARY(length)
  | CREATETIME
  | MODIFYTIME

table_option:
    EXPIRE_INFO [=] ‘expire info’
  | TABLET_MAX_SIZE [=] value
  | REPLICA_NUM [=] value
  | COMPRESS_METHOD [=] ‘compress method name’
  | USE_BLOOM_FILTER [=] {0 | 1}

table_option中，EXPIRE_INFO是OceanBase提供自动删除过期数据功能配备。TABLET_MAX_SIZE是这个表Tablet最大尺寸，单位为字节，默以为256MB。REPLICA_NUM为这个表tablet复本数，默认值为3。COMPRESS_METHOD为存储数据时使用压缩办法名，当前提供办法有none（默认值，表达不作压缩）、lzo_1_0、snappy_1_0等。USE_BLOOM_FILTER是配备对于本表OceanBase读取数据时与否使用Bloom Filter，默认值为0。
注意，NOT NULL，DEFAULT，AUTO_INCREMENT选项可以浮现，但当前尚未实现。
建表时候，必要指定Primary Key，由于OB内部数据以btree为索引，按照Primary Key排序。同样因素，插入数据时，Primary Key列值不容许为NULL。
如果指定IF NOT EXISTS，则如果同名表已经存在，不报错。
5.1.2. drop table语句
DROP TABLE [IF EXISTS]
    tbl_name [，tbl_name] ...

如果指定IF EXISTS，则如果某个表不存在，不报错。
5.1.3. alter table语句
ALTER TABLE
    tbl_name alter_column_action [,alter_column_action] ...

alter_column_action:
ADD [COLUMN] col_name column_definition
| DROP [COLUMN] col_name

当前只支持增长和删除列。
5.2. 数据操作语句
5.2.1. select语句
5.2.1.1. 基本句法
SELECT
[ALL | DISTINCT]
select_expr [，select_expr ...]
FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC]，...]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC]，...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
5.2.1.2. Join句法
SELECT语法中，FROM子句中table_references可以用下面语法来表达Join关系：
table_references:
table_reference [，table_reference] ...

table_reference:
table_factor
| join_table

table_factor:
tbl_name [[AS] alias]
| table_subquery [AS] alias
| ( table_references )

join_table:
table_reference [INNER] JOIN table_factor join_condition
| table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition

join_condition:
ON conditional_expr

5.2.1.3. 集合操作
SELECT ...
set_op SELECT ...
...
[ORDER BY {col_name | expr | position}
[ASC | DESC]，...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

set_op:
UNION [ALL | DISTINCT]
| INTERSECT [ALL | DISTINCT]
| EXCEPT [ALL | DISTINCT]
5.2.1.4. DUAL虚拟表
SELECT
[ALL | DISTINCT]
select_expr [，select_expr ...]
[FROM DUAL
[WHERE where_condition]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

DUAL是一