insert into on duplicate key_mysql数据库同步解决方案

1. Could our database support multi-database under one single instance? 2. Could we support temporary table? 3. Could we support view? 4. Could we support stored procedure and functions? 5. Could eXtremeDB be integrated into BI app like Mi

> 1. Could our database support multi-database under one single instance?

>

> 2. Could we support temporary table?

>

> 3. Could we support view?

>

> 4. Could we support stored procedure and functions?

>

> 5. Could eXtremeDB be integrated into BI app like Microstrategy ?

>

> 6. Could we support Architeture like MPP so that all the data could be distributed onto different nodes evenly.

>

> 7. What is the average compression ratio or ratio range typically ? Could we reach the ratio of 10:1

1. Yes, eXtremeDB can open and maintain connections to multiple databases from a single task or process. Each database will require its own “database connection” (the database connection is a way to identify the database to the application)

2. The eXtremeDB databases are normally defined statically with the schema. eXtremeDB does not support temporary tables in the normal SQL meaning of the word (tables that exist within a session). That’s said, there is a limited support for “create table” statement to create in memory tables (or persistent tables. The in-mmeory tables created via the create statement will only exists during the current session, so essentially they are the same as “normal” temporary tables

3. There is a limited support for views (see the eXtremeSQL User’s Guide)

In order to create a view , the application must have the “Views” class defined in the schema:

class Views

{

string name;

string body;

treepk;

};

Only the name of the table and the names of fields are essential. “name” contains the name of the view and the body contains the text of view expression.

View are created in standard SQL manner:

CREATE VIEW name AS select-expression;

XSQL>create view SV as select sid from S; insert into S (sid,sname)

XSQL>values (1,1); select * from SV;

sid

——————————————————————————

1

XSQL>drop view SV;

Things to be aware about

a. Views are implemented as nested select so the query above will be translated to

select * from (select sid from S);

The xsql optimizer is not always smart enough to efficiently transform nested queries. So sometimes query with views will be less efficient than one written manually without views. I can not give you precise example now or give you some estimation how much presence of view can degrade performance of query.

b. The information about created views is stored in Views table. If you want view definition to be persistent , you should define table Views as “persistent”. Certainly it is relevant for disk database only.

c. Views are read-only. Updateable views are not supported. It means it is not possible to do something like:

insert into SV (sid) values (1);

4. Storage procedures are not supported since the eXtremeDB is an embedded database as opposed to a server-type database. SQL Support for functions in SQL is limited to the API — functions are not stored. SQL supports a number of built-in string and math functions as well as user-defined functions that can be used in SQL statements. For example

static String* dateformat( McoSql::Value* date) { char str[64]; int date_val = (int)date->intValue(); int yy = date_val / 10000; int mm = date_val / 100 % 100; int dd = date_val % 100; // Format dd.mm.yyyy assuming all dates are after 2000 eXtremeSQL User Guide version 4.5 page 33

sprintf( str, “%d.%d.20%02d”, dd, mm, yy ); return String::create( str ); } static SqlFunctionDeclaration udf( tpString, // tpInt is the return value of the UDF “dateformat”, // the name of the function as we’ll use it in a query (void*)dateformat, // the function pointer

1 // the number of arguments to the UDF ); The UDF can then be called in a normal SQL select statement. For example, the following statement formats the date values in the result set by calling the UDF:

select dateformat(date_val) from Contributions;

5. There is currently no integration between the eXtremeDB data source and MicroStrategy BI environment. However we are considering the integration of the FE with a number of platforms, including the MicroStrategy Intelligence Server

6. Again, sharding (as a tool within the MPP database architecture) is not currently supported. We are in the process adding sharding and will be happy to consider requirements

7. Compression is supported for the large data data sets (sequences) through RLE. Its not possible to quantify the compression rate as not depends on the data being compressed. By the same token there is no “typical” compression rate.

本文原创发布php中文网，转载请注明出处，感谢您的尊重！