MySQL:5.6/5.7/8.0结果不一样一例
2021-03-24 本文已影响0人
重庆八怪
用户测试：
mysql5.6 测试：
root@zjgldb.sock>insert into test1 values('1',6);
Query OK, 1 row affected (0.01 sec)
root@zjgldb.sock>insert into test2 values('1',8);
Query OK, 1 row affected (0.02 sec)
root@zjgldb.sock>select * from test1;
+------+-------+
| id | score |
+------+-------+
| 1 | 6 |
+------+-------+
1 row in set (0.00 sec)
root@zjgldb.sock>select * from test2;
+------+-------+
| id | score |
+------+-------+
| 1 | 8 |
+------+-------+
1 row in set (0.00 sec)
root@zjgldb.sock>update test1 a join test2 b on a.id=b.id set a.score=a.score+1,b.score=a.score+1 where a.id=1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
root@zjgldb.sock>select * from test1;
+------+-------+
| id | score |
+------+-------+
| 1 | 7 |
+------+-------+
1 row in set (0.00 sec)
root@zjgldb.sock>select * from test2;
+------+-------+
| id | score |
+------+-------+
| 1 | 7 |
+------+-------+
1 row in set (0.00 sec)
mysql5.7 测试：
mysql> create table test1(id varchar(2),score int(11));
Query OK, 0 rows affected (0.01 sec)
mysql> create table test2(id varchar(2),score int(11));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test1 values('1',6);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test2 values('1',8);
Query OK, 1 row affected (0.00 sec)
mysql> update test1 a join test2 b on a.id=b.id set a.score=a.score+1,b.score=a.score+1 where a.id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 2 Changed: 1 Warnings: 0
mysql> select * from test1;
+------+-------+
| id | score |
+------+-------+
| 1 | 7 |
+------+-------+
1 row in set (0.00 sec)
mysql> select * from test2;
+------+-------+
| id | score |
+------+-------+
| 1 | 8 |
+------+-------+
1 row in set (0.00 sec)
mysql8.0测试：
mysql> insert into test1 values('1',6);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test2 values('1',8);
Query OK, 1 row affected (0.01 sec)
mysql> update test1 a join test2 b on a.id=b.id set a.score=a.score+1,b.score=a.score+1 where a.id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 2 Changed: 1 Warnings: 0
mysql> select * from test1;
+------+-------+
| id | score |
+------+-------+
| 1 | 7 |
+------+-------+
1 row in set (0.00 sec)
mysql> select * from test2;
+------+-------+
| id | score |
+------+-------+
| 1 | 8 |
+------+-------+
1 row in set (0.00 sec)
已经提交了一个Bug：
https://bugs.mysql.com/bug.php?id=103085&thanks=2¬ify=71
不知道咋办，反正别这么用就行了，问题天天有。蛋疼。。。。
mysql5.6 测试：
root@zjgldb.sock>insert into test1 values('1',6);
Query OK, 1 row affected (0.01 sec)
root@zjgldb.sock>insert into test2 values('1',8);
Query OK, 1 row affected (0.02 sec)
root@zjgldb.sock>select * from test1;
+------+-------+
| id | score |
+------+-------+
| 1 | 6 |
+------+-------+
1 row in set (0.00 sec)
root@zjgldb.sock>select * from test2;
+------+-------+
| id | score |
+------+-------+
| 1 | 8 |
+------+-------+
1 row in set (0.00 sec)
root@zjgldb.sock>update test1 a join test2 b on a.id=b.id set a.score=a.score+1,b.score=a.score+1 where a.id=1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
root@zjgldb.sock>select * from test1;
+------+-------+
| id | score |
+------+-------+
| 1 | 7 |
+------+-------+
1 row in set (0.00 sec)
root@zjgldb.sock>select * from test2;
+------+-------+
| id | score |
+------+-------+
| 1 | 7 |
+------+-------+
1 row in set (0.00 sec)
mysql5.7 测试：
mysql> create table test1(id varchar(2),score int(11));
Query OK, 0 rows affected (0.01 sec)
mysql> create table test2(id varchar(2),score int(11));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test1 values('1',6);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test2 values('1',8);
Query OK, 1 row affected (0.00 sec)
mysql> update test1 a join test2 b on a.id=b.id set a.score=a.score+1,b.score=a.score+1 where a.id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 2 Changed: 1 Warnings: 0
mysql> select * from test1;
+------+-------+
| id | score |
+------+-------+
| 1 | 7 |
+------+-------+
1 row in set (0.00 sec)
mysql> select * from test2;
+------+-------+
| id | score |
+------+-------+
| 1 | 8 |
+------+-------+
1 row in set (0.00 sec)
mysql8.0测试：
mysql> insert into test1 values('1',6);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test2 values('1',8);
Query OK, 1 row affected (0.01 sec)
mysql> update test1 a join test2 b on a.id=b.id set a.score=a.score+1,b.score=a.score+1 where a.id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 2 Changed: 1 Warnings: 0
mysql> select * from test1;
+------+-------+
| id | score |
+------+-------+
| 1 | 7 |
+------+-------+
1 row in set (0.00 sec)
mysql> select * from test2;
+------+-------+
| id | score |
+------+-------+
| 1 | 8 |
+------+-------+
1 row in set (0.00 sec)
- item 结构
Item_func_plus
包含2个操作数两个操作数存在Item_func的argc中 同时也是指针数组 也为item 实际为Item_field
/ \
Item_field ---> Field *field Item_field ---> Field *field
| |
| |
Field_long --包含实际数据ptr Field_long --包含实际数据ptr
ptr为一根指针，指向了innodb 获取后转换为mysql格式的行值，注意Field_long::store会有一个longstore(ptr,res)操作，每次每个字段通过Item_func_plus计算后的值将填入内存空间。因此导致如果更改值后再次获取会出现变动类似，如果要保证不变则需要拷贝一份内存出来，类型5.6
(gdb) p ((Item_field*)args[0])->field->ptr
$69 = (uchar *) 0x7fff90acf781 "8"
(gdb) p info->record
$57 = (uchar *) 0x7fff90acf780 "\370\066"
(gdb) x/32bx 0x7fff90acf780
0x7fff90acf780: 0xf8 0x36 0x00 0x00 0x00 0x38 0x00 0x00
0x7fff90acf788: 0x00 0x01 0x67 0x00 0x00 0x00 0x00 0x00
0x7fff90acf790: 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00
0x7fff90acf798: 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00
(gdb) x/32bx 0x7fff90acf781
0x7fff90acf781: 0x36 0x00 0x00 0x00 0x38 0x00 0x00 0x00
0x7fff90acf789: 0x01 0x67 0x00 0x00 0x00 0x00 0x00 0x00
0x7fff90acf791: 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00
0x7fff90acf799: 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0xf8
(gdb) p rfield->ptr
$61 = (uchar *) 0x7fff90acf785 "8"
(gdb) x/32bx 0x7fff90acf785
0x7fff90acf785: 0x38 0x00 0x00 0x00 0x01 0x67 0x00 0x00
0x7fff90acf78d: 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00
0x7fff90acf795: 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00
0x7fff90acf79d: 0x00 0x00 0x00 0xf8 0x36 0x00 0x00 0x00
(gdb) x/32bx info->record
0x7fff90acf780: 0xf8 0x38 0x00 0x00 0x00 0x3a 0x00 0x00
0x7fff90acf788: 0x00 0x01 0x67 0x00 0x00 0x00 0x00 0x00
0x7fff90acf790: 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00
0x7fff90acf798: 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00
5.7 一个地址
Breakpoint 18, Item_func_plus::int_op (this=0x7fff90006bf8) at /opt/percona-server-locks-detail-5.7.22/sql/item_func.cc:1790
1790 longlong val0= args[0]->val_int();
(gdb) n
1791 longlong val1= args[1]->val_int();
(gdb) p ((Item_field*)args[0])->field->ptr
$86 = (uchar *) 0x7fff909eacf4 "G"
(gdb) p ((Item_field*)args[0])->field
$87 = (Field *) 0x7fff909eadf0
(gdb) n
2021-03-24T11:21:15.651272Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 35995ms. The settings might not be optimal. (flushed=0, during the time.)
1792 longlong res= val0 + val1;
(gdb) n
1793 bool res_unsigned= FALSE;
(gdb) n
1795 if ((null_value= args[0]->null_value || args[1]->null_value))
(gdb) n
1803 if (args[0]->unsigned_flag)
(gdb) n
1820 if (args[1]->unsigned_flag)
(gdb) n
1836 if (val0 >=0 && val1 >= 0)
(gdb) n
1837 res_unsigned= TRUE;
(gdb) n
1842 return check_integer_overflow(res, res_unsigned);
(gdb) n
1846 }
(gdb) n
Item_func_numhybrid::val_int (this=0x7fff90006bf8) at /opt/percona-server-locks-detail-5.7.22/sql/item_func.cc:1480
1480 }
(gdb) n
Item::save_in_field_inner (this=0x7fff90006bf8, field=0x7fff909eadf0, no_conversions=false) at /opt/percona-server-locks-detail-5.7.22/sql/item.cc:6883
6883 if (null_value)
(gdb) n
6885 field->set_notnull();
(gdb) n
6886 return field->store(nr, unsigned_flag);
(gdb) n
Breakpoint 30, Field_long::store (this=0x7fff909eadf0, nr=72, unsigned_val=false) at /opt/percona-server-locks-detail-5.7.22/sql/field.cc:4121
4121 ASSERT_COLUMN_MARKED_FOR_WRITE;
(gdb) p ptr
$88 = (uchar *) 0x7fff909eacf4 "G"
(gdb)
5.6不是一个地址
(gdb) n
1316 longlong val1= args[1]->val_int();
(gdb) p ((Item_field*)args[0])->field->ptr
$6 = (uchar *) 0x7fff18045afc "\026"
(gdb) p ((Item_field*)args[0])->field
$7 = (Field *) 0x7fff1803f8a0
(gdb) n
1317 longlong res= val0 + val1;
(gdb) n
1318 bool res_unsigned= FALSE;
(gdb) n
1320 if ((null_value= args[0]->null_value || args[1]->null_value))
(gdb) n
1328 if (args[0]->unsigned_flag)
(gdb) n
1345 if (args[1]->unsigned_flag)
(gdb) n
1361 if (val0 >=0 && val1 >= 0)
(gdb) n
1362 res_unsigned= TRUE;
(gdb) n
1367 return check_integer_overflow(res, res_unsigned);
(gdb) n
1371 }
(gdb) n
Item_func_numhybrid::val_int (this=0x7fff18006a58) at /opt/mysql/mysql-5.6.25/sql/item_func.cc:1013
1013 }
(gdb) n
Item::save_in_field (this=0x7fff18006a58, field=0x7fff18057068, no_conversions=false) at /opt/mysql/mysql-5.6.25/sql/item.cc:6339
6339 if (null_value)
(gdb) n
6341 field->set_notnull();
(gdb) n
6342 error=field->store(nr, unsigned_flag);
(gdb) S
Field_long::store (this=0x7fff18057068, nr=23, unsigned_val=false) at /opt/mysql/mysql-5.6.25/sql/field.cc:3750
3750 ASSERT_COLUMN_MARKED_FOR_WRITE;
(gdb) p ptr
$8 = (uchar *) 0x7fff1805730f ""
