bluemeteor

问题：如何避免在replication关系中的俩个master-master的数据库组之间的自增ID冲突问题

解决办法:根据master数量来分别自增，如数据库A奇数自增，数据库B偶数自增

But,mysql似乎没有提供自增增量的设定，以下是针对这个问题的阅读笔记

以下为mysql4.1 ref中关于AUTO_INCREMENT的一个基本介绍

• An integer column can have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an indexed AUTO_INCREMENT column, the column is set to the next sequence value. Typically this is value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1.
首先 在mysql中，整数型的字段可以配置一个额外的属性就是 AUTO_INCREMENT,当你试图向一个被索引的AUTO_INCREMENT字段中插入一个null或者0值时候。实际字段取值会被设置成为下一个’序列’值.
也就是说是value+1，而其中的value值为这个字段在表中的当前最大值.
AUTO_INCREMENT的序列初始值为1
To retrieve an AUTO_INCREMENT value after inserting a row, use the LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function. See Section 12.9.3, “Information Functions”, and Section 17.2.3.35, “mysql_insert_id()”.
如果你想获得你刚刚插入操作后所产生的最新的AUTO_INCREMENT值。那么你可以使用LAST_INSERT_ID()这个SQL函数或者使用C里面所集成的mysql_insert_id()这个API。PHP同学对这个API也会很熟悉得了.不过JAVA就要通过JDBC来读了>_<
As of MySQL 4.1.1, if the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled, you can store 0 in AUTO_INCREMENT columns as 0 without generating a new sequence value. See Section 5.2.5, “The Server SQL Mode”.
在mysql 4.1.1以后的版本中，用户可以通过配置NO_AUTO_VALUE_ON_ZERO这个sql mode来实现当一个0值试图插入一个AUTO_INCREMENT字段时能保证字段的插入值仍然为0.看了一下，只要是为了防止使用mysqldump时候对前后数据库的内容造成差异所设定的一个选项。基本上除了这个是用不到的Note: There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. As of MySQL 3.23, an AUTO_INCREMENT column works properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers “wrap” over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0.
每个表中只能存在一个AUTO_INCREMENT的字段。同时这个字段必须被索引，而且也不能为这个字段设置一个默认值(很劲爆的说明).在mysql3.23以前的版本中，AUTO_INCREMENT的字段只能保存正整数，当用户将一个负数付值给一个AUTO_INCREMENT字段时，系统会把负数认为是一个怒大的正数.这个是可以理解的……
For MyISAM and BDB tables, you can specify an AUTO_INCREMENT secondary column in a multiple-column key. See Section 3.6.9, “Using AUTO_INCREMENT”.
见下段
To make MySQL compatible with some ODBC applications, you can find the AUTO_INCREMENT value for the last inserted row with the following query:
SELECT * FROM tbl_name WHERE auto_col IS NULL
这个是一条获取最后插入的AUTO_INCREMENT值的SQL语句

唔,没有找到关于增量的定义，继续察看ref

以下是ref中3.6.9 section内容 真是个好记的章节名
Using AUTO_INCREMENT
The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows:
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);

INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');

SELECT * FROM animals;
Which returns:
+----+---------+
| id | name |
+----+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+----+---------+
以上没什么新鲜的东西，一个华丽的多行插入后自动增长的ID序列
You can retrieve the most recent AUTO_INCREMENT value with the LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function. These functions are connection-specific, so their return values are not affected by another connection which is also performing inserts.
继续介绍了刚刚提到的俩个函数，同时说明俩个函数的返回值是连接独立的，也就是说用户A访问某个页面与DB建立的connA，同时用户B和C也建立的connB和connC，这三个conn的连接中函数的返回值是针对每个连接所发起的INSERT独立，这一点我深刻表示怀疑，因为我的理解AUTO_INCREMENT所生成的sequence值应该是个全局的概念.如果有机会我会尝试一下这点
Note: For a multiple-row insert, LAST_INSERT_ID() and mysql_insert_id() actually return the AUTO_INCREMENT key from the first of the inserted rows. This allows multiple-row inserts to be reproduced correctly on other servers in a replication setup.
如果多行插入后。函数会返回第一行插入的sequence值.
For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.
CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (grp,id)
);

INSERT INTO animals (grp,name) VALUES
('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','lax'),('mammal','whale'),
('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;
Which returns:
+--------+----+---------+
| grp | id | name |
+--------+----+---------+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+--------+----+---------+
Note that in this case (when the AUTO_INCREMENT column is part of a multiple-column index), AUTO_INCREMENT values are reused if you delete the row with the biggest AUTO_INCREMENT value in any group. This happens even for MyISAM tables, for which AUTO_INCREMENT values normally are not reused.
If the AUTO_INCREMENT column is part of multiple indexes, MySQL will generate sequence values using the index that begins with the AUTO_INCREMENT column, if there is one. For example, if the animals table contained indexes PRIMARY KEY (grp, id) and INDEX (id), MySQL would ignore the PRIMARY KEY for generating sequence values. As a result, the table would contain a single sequence, not a sequence per grp value.
这个是非常华丽的一点，以前没有注意到mysql提供这方面的特性，当AUTO_INCREMENT出现在联合索引的第二或者更靠后的字段中，他们会针对每个第一索引字段分别自增，具体效果可以参照例子. 这个特性在进行数据规划排列时是非常够劲的… 但是，只能使用在MyISAM和BDB的表类型中…To start with an AUTO_INCREMENT value other than 1, you can set that value with CREATE TABLE or ALTER TABLE, like this:
mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;
Note that this feature is available for InnoDB tables only as of MySQL 4.1.12.
可以设定AUTO_INCREMENT的初始值.

看到最后终于看到一个和我出发点有点关系得说明，不过关于我所期待offset还是没有出现，看来目前来讲mysql4.x还没有这个功能.至于mysql5目前还没有任何愿望来使用它

So，关于在master-master之间进行ID分布式增长，通过AUTO_INCREMENT是不行的，只有通过程序实现了

Bye！