基于statement复制下NULL、RAND、UUID的测试

  • G8bao7

    2014-02-19 22:38:35
  • MySQL
  • 原创
版本:5.5.29

测试方法：
slave先停止同步，制造同步延迟
master分别使用NULL、RAND()、UUID()进行update
slave延迟3s以上打开同步
比较主从数据是否一致

结论: NULL、RAND()一致。UUID()不一致
原因:
    NULL: binlog保存原sql
    RAND(): binlog除了原sql，同时记录了种子值
    UUID: 
        MASTER : 4b1ae5c8-9952-11e3-b7ca-00151760d8ec
        SLAVE   : 4b2744b2-9952-11e3-afc9-00151760d8ec             
        UUID格式共36个字符:aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee
        前三组值是时间戳换算过来的；
        第四组值是暂时性保持时间戳的唯一性。例如，使用夏令时；
        第五组值是一个IEE 802的节点标识值，它是空间上唯一的。若后者不可用，则用一个随机数字替换。假如主机没有网卡，或者我们不知道如何在某系统下获得机器地址，则空间唯一性就不能得到保证，即使这杨，出现重复值的机率还是非常小的。    
        官方说明：http://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functions.html#function_uuid
        疑问:
            binlog有时间戳为何不用？
            最后一组的数值为何是一样，理论上不同机器(主从)上至少最后一位肯定不同？因为测试的主从环境是一台机器上的两个端口，并不是两台机器

一、表结构及数据

点击(此处)折叠或打开

  1. -- 表结构
  2. DROP TABLE IF EXISTS `test_140219_sbr`;
  3. CREATE TABLE `test_140219_sbr` (
  4.   `id` INT(11) NOT NULL AUTO_INCREMENT,
  5.   `sid` INT(11) NOT NULL DEFAULT \'0\',
  6.   `val` VARCHAR(255) DEFAULT NULL,
  7.   `upddt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  8.   `mupddt` DATETIME DEFAULT NULL,
  9.   PRIMARY KEY (`id`)
  10. ) ENGINE=INNODB DEFAULT CHARSET=utf8;
  11. -- 插入数据
  12. INSERT INTO `test_140219_sbr`(`id`) VALUES ('1'),('2'),('3'),('4');

二、测试sql及相应的binlog
SET @ct:=NOW();
SELECT SLEEP(1);
-- 字符串'NULL'
UPDATE `test_140219_sbr` SET sid=@@server_id,`val` = 'NULL',`upddt`=NOW(),`mupddt`=@ct WHERE id=1;

点击(此处)折叠或打开

  1. # at 4588
  2. #140219 18:40:47 server id 3307 end_log_pos 4672 Query thread_id=4 exec_time=0 error_code=0
  3. SET TIMESTAMP=1392806447/*!*/;
  4. BEGIN
  5. /*!*/;
  6. # at 4672
  7. #140219 18:40:47 server id 3307 end_log_pos 4726 User_var
  8. SET @`ct`:=_latin1 0x323031342D30322D31392031383A34303A3436 COLLATE `latin1_swedish_ci`/*!*/;
  9. # at 4726
  10. #140219 18:40:47 server id 3307 end_log_pos 4921 Query thread_id=4 exec_time=0 error_code=0
  11. SET TIMESTAMP=1392806447/*!*/;
  12. -- 字符串'NULL'
  13. UPDATE `test_140219_sbr` SET sid=@@server_id,`val` = 'NULL',`upddt`=NOW(),`mupddt`=@ct WHERE id=1
  14. /*!*/;
  15. # at 4921
  16. #140219 18:40:47 server id 3307 end_log_pos 4948 Xid = 376
  17. COMMIT/*!*/;
  18. # at 4948

-- NULL值
UPDATE `test_140219_sbr` SET sid=@@server_id,`val` = NULL,`upddt`=NOW(),`mupddt`=@ct WHERE id=2;

点击(此处)折叠或打开

  1. # at 4948
  2. #140219 18:40:47 server id 3307 end_log_pos 5032 Query thread_id=4 exec_time=0 error_code=0
  3. SET TIMESTAMP=1392806447/*!*/;
  4. BEGIN
  5. /*!*/;
  6. # at 5032
  7. #140219 18:40:47 server id 3307 end_log_pos 5086 User_var
  8. SET @`ct`:=_latin1 0x323031342D30322D31392031383A34303A3436 COLLATE `latin1_swedish_ci`/*!*/;
  9. # at 5086
  10. #140219 18:40:47 server id 3307 end_log_pos 5271 Query thread_id=4 exec_time=0 error_code=0
  11. SET TIMESTAMP=1392806447/*!*/;
  12. -- NULL值
  13. UPDATE `test_140219_sbr` SET sid=@@server_id,`val` = NULL,`upddt`=NOW(),`mupddt`=@ct WHERE id=2
  14. /*!*/;
  15. # at 5271
  16. #140219 18:40:47 server id 3307 end_log_pos 5298 Xid = 378
  17. COMMIT/*!*/;
  18. # at 5298

-- RAND
UPDATE `test_140219_sbr` SET sid=@@server_id,`val` = RAND(),`upddt`=NOW(),`mupddt`=@ct WHERE id=3;

点击(此处)折叠或打开

  1. #140219 18:40:47 server id 3307 end_log_pos 5382 Query thread_id=4 exec_time=0 error_code=0
  2. SET TIMESTAMP=1392806447/*!*/;
  3. BEGIN
  4. /*!*/;
  5. # at 5382
  6. #140219 18:40:47 server id 3307 end_log_pos 5417 Rand
  7. SET @@RAND_SEED1=54715179, @@RAND_SEED2=199865937/*!*/;
  8. # at 5417
  9. #140219 18:40:47 server id 3307 end_log_pos 5471 User_var
  10. SET @`ct`:=_latin1 0x323031342D30322D31392031383A34303A3436 COLLATE `latin1_swedish_ci`/*!*/;
  11. # at 5471
  12. #140219 18:40:47 server id 3307 end_log_pos 5655 Query thread_id=4 exec_time=0 error_code=0
  13. SET TIMESTAMP=1392806447/*!*/;
  14. -- RAND
  15. UPDATE `test_140219_sbr` SET sid=@@server_id,`val` = RAND(),`upddt`=NOW(),`mupddt`=@ct WHERE id=3
  16. /*!*/;
  17. # at 5655
  18. #140219 18:40:47 server id 3307 end_log_pos 5682 Xid = 380
  19. COMMIT/*!*/;
  20. # at 5682

-- UUID
UPDATE `test_140219_sbr` SET sid=@@server_id,`val` = UUID(),`upddt`=NOW(),`mupddt`=@ct WHERE id=4;

点击(此处)折叠或打开

  1. #140219 18:40:47 server id 3307 end_log_pos 5766 Query thread_id=4 exec_time=0 error_code=0
  2. SET TIMESTAMP=1392806447/*!*/;
  3. BEGIN
  4. /*!*/;
  5. # at 5766
  6. #140219 18:40:47 server id 3307 end_log_pos 5820 User_var
  7. SET @`ct`:=_latin1 0x323031342D30322D31392031383A34303A3436 COLLATE `latin1_swedish_ci`/*!*/;
  8. # at 5820
  9. #140219 18:40:47 server id 3307 end_log_pos 6004 Query thread_id=4 exec_time=0 error_code=0
  10. SET TIMESTAMP=1392806447/*!*/;
  11. -- UUID
  12. UPDATE `test_140219_sbr` SET sid=@@server_id,`val` = UUID(),`upddt`=NOW(),`mupddt`=@ct WHERE id=4
  13. /*!*/;
  14. # at 6004
  15. #140219 18:40:47 server id 3307 end_log_pos 6031 Xid = 382
  16. COMMIT/*!*/;
  17. # at 6031


三、主从数据
MASTER

点击(此处)折叠或打开

  1. mysql> SELECT id,sid,ifnull(val,'null值') as 'val',upddt,mupddt FROM `test_140219_sbr`\G;
  2. *************************** 1. row ***************************
  3.     id: 1
  4.    sid: 3307
  5.    val: NULL
  6.  upddt: 2014-02-19 18:40:47
  7. mupddt: 2014-02-19 18:40:46
  8. *************************** 2. row ***************************
  9.     id: 2
  10.    sid: 3307
  11.    val: null值
  12.  upddt: 2014-02-19 18:40:47
  13. mupddt: 2014-02-19 18:40:46
  14. *************************** 3. row ***************************
  15.     id: 3
  16.    sid: 3307
  17.    val: 0.3390121034709849
  18.  upddt: 2014-02-19 18:40:47
  19. mupddt: 2014-02-19 18:40:46
  20. *************************** 4. row ***************************
  21.     id: 4
  22.    sid: 3307
  23.    val: 4b1ae5c8-9952-11e3-b7ca-00151760d8ec
  24.  upddt: 2014-02-19 18:40:47
  25. mupddt: 2014-02-19 18:40:46

SLAVE

点击(此处)折叠或打开

  1. mysql> SELECT id,sid,ifnull(val,'null值') as 'val',upddt,mupddt FROM `test_140219_sbr`\G;
  2. *************************** 1. row ***************************
  3.     id: 1
  4.    sid: 3308
  5.    val: NULL
  6.  upddt: 2014-02-19 18:40:47
  7. mupddt: 2014-02-19 18:40:46
  8. *************************** 2. row ***************************
  9.     id: 2
  10.    sid: 3308
  11.    val: null值
  12.  upddt: 2014-02-19 18:40:47
  13. mupddt: 2014-02-19 18:40:46
  14. *************************** 3. row ***************************
  15.     id: 3
  16.    sid: 3308
  17.    val: 0.3390121034709849
  18.  upddt: 2014-02-19 18:40:47
  19. mupddt: 2014-02-19 18:40:46
  20. *************************** 4. row ***************************
  21.     id: 4
  22.    sid: 3308
  23.    val: 4b2744b2-9952-11e3-afc9-00151760d8ec
  24.  upddt: 2014-02-19 18:40:47
  25. mupddt: 2014-02-19 18:40:46





请使用浏览器的分享功能分享到微信等