Skip to content

Latest commit

 

History

History
123 lines (92 loc) · 4.2 KB

6.Differernce_from_MySQL_Server.md

File metadata and controls

123 lines (92 loc) · 4.2 KB

6. 与MySQL Server的差异化描述

这里主要描述一些与MySQL Server不同的行为，这些行为不是bug，是分布式场景下的一些正常的行为表现，但与已知的MySQL行为不一致。

6.1 事务中遇到主键冲突需要显式回滚，MySQL不需要

具体表现如下:

MYSQL行为:

[test_yhq]>select * from char_columns_4;
+----+--------+
| id | c_char |
+----+--------+
|  1 | xx |
|  4 | z  |
+----+--------+
2 rows in set (0.02 sec)
[test_yhq]>begin;
Query OK, 0 rows affected (0.01 sec)

[test_yhq]>insert into char_columns_4 values(1,'yy');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
[test_yhq]>insert into char_columns_4 values(2,'yy');
Query OK, 1 row affected (0.00 sec)

[test_yhq]>commit;
Query OK, 0 rows affected (0.02 sec)

dble行为:

[testdb]>select * from sharding_four_node order by id;
+----+--------+-----------+
| id | c_flag | c_decimal |
+----+--------+-----------+
|  1 | 1_1    |    1.0000 |
|  2 | 2      |    2.0000 |
|  3 | 3      |    3.0000 |
+----+--------+-----------+
3 rows in set (0.28 sec)
 
begin;
Query OK, 0 rows affected (0.01 sec)

[testdb]>insert into sharding_four_node values(1,'1',1.0);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
[testdb]>insert into sharding_four_node values(13,'13',13.0);
ERROR 1003 (HY000): Transaction error, need to rollback.Reason:[ errNo:1062 Duplicate entry '1' for key 'PRIMARY']
[testdb]>commit;
ERROR 1003 (HY000): Transaction error, need to rollback.Reason:[ errNo:1062 Duplicate entry '1' for key 'PRIMARY']

6.2 INSERT自增序列表由dble生成，不能显式指定自增列，MySQL可以。

具体表现如下:

MySQL行为:

desc mysql_autoinc;
+--------+------------+------+-----+---------+----------------+
| Field  | Type       | Null | Key | Default | Extra          |
+--------+------------+------+-----+---------+----------------+
| c_char | char(255)  | YES  |     | NULL    |                |
| id     | bigint(20) | NO   | PRI | NULL    | auto_increment |
+--------+------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

[test_yhq]>insert into mysql_autoinc values('1',1);
Query OK, 1 row affected (0.01 sec)

dble行为

desc sharding_four_node_autoinc;
+--------+------------+------+-----+---------+----------------+
| Field  | Type       | Null | Key | Default | Extra          |
+--------+------------+------+-----+---------+----------------+
| c_char | char(255)  | YES  |     | NULL    |                |
| id     | bigint(20) | NO   | PRI | NULL    | auto_increment |
+--------+------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
[testdb]>insert into sharding_four_node_autoinc values('2',2);
ERROR 1064 (HY000): In insert Syntax, you can't set value for Autoincrement column!

6.3 ADD "show all tables ""

The optional ALL modifier causes SHOW TABLES to display a second output column with values of BASE TABLE for a table ,VIEW for a view, SHARDING TABLE for a sharding table and GLOBAL TABLE for a global table.

具体表现如下:

[testdb]>show all tables;
+----------------------------+----------------+
| Tables in testdb           | Table_type     |
+----------------------------+----------------+
| global_four_node           | GLOBAL TABLE   |
| global_four_node_autoinc   | GLOBAL TABLE   |
| global_two_node            | GLOBAL TABLE   |
| sbtest1                    | SHARDING TABLE |
| sharding_four_node         | SHARDING TABLE |
| sharding_four_node2        | SHARDING TABLE |
| sharding_four_node_autoinc | SHARDING TABLE |
| sharding_two_node          | SHARDING TABLE |
| single                     | SHARDING TABLE | 
| customer                   | BASE TABLE     |
| district                   | BASE TABLE     |
+----------------------------+----------------+
11 rows in set (0.02 sec)

6.4 去除了增删改的message 信息

具体表现如下:

MySQL行为:

mysql> insert into sharding_two_node values(9,'9',9.0),(10,'10',10.0); Query OK, 2 rows affected (0.24 sec) Records: 2 Duplicates: 0 Warnings: 0

dble行为:

mysql> insert into sharding_two_node values(11,'11',11.0),(12,'12',12.0); Query OK, 2 rows affected (0.49 sec)