美文网首页
MySQL:5.6/5.7/8.0结果不一样一例

MySQL:5.6/5.7/8.0结果不一样一例

作者: 重庆八怪 | 来源:发表于2021-03-24 20:51 被阅读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&notify=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 ""

    相关文章

      网友评论

          本文标题：MySQL:5.6/5.7/8.0结果不一样一例

          本文链接：https://www.haomeiwen.com/subject/yhtkhltx.html

          延伸阅读

          深度阅读

          • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|MySQL:5.6/5.7/8.0结果不一样一例|投稿指南|网站地图|RSS订阅|排版工具|手机版

          提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

          Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

          备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

          本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

          所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！