欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

使用limit分页查询时,做delete操作,会导致丢失数据

程序员文章站 2022-04-16 07:58:54
使用limit分页查询时,做delete操作,会导致丢失数据 [TOC] 一、准备数据 1.1 mysql数据脚本 mysql SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; Table structure for test_so_item DROP T ......

使用limit分页查询时,做delete操作,会导致丢失数据

目录

一、准备数据

1.1 mysql数据脚本

set names utf8mb4;
set foreign_key_checks = 0;

-- ----------------------------
-- table structure for test_so_item
-- ----------------------------
drop table if exists `test_so_item`;
create table `test_so_item`  (
  `id` bigint(20) not null auto_increment,
  `line_no` varchar(20) character set utf8mb4 collate utf8mb4_general_ci null default null,
  `sku` varchar(255) character set utf8mb4 collate utf8mb4_general_ci null default null,
  `qty` decimal(4, 2) null default null,
  primary key (`id`) using btree
) engine = innodb auto_increment = 1 character set = utf8mb4 collate = utf8mb4_general_ci row_format = dynamic;

-- ----------------------------
-- records of test_so_item
-- ----------------------------
insert into `test_so_item` values (1, '00020', 'a0001', 10.00);
insert into `test_so_item` values (2, '00010', 'nt9531', 1.00);
insert into `test_so_item` values (3, '00030', 'a0002', 2.00);
insert into `test_so_item` values (4, '00040', 'a0003', 5.00);


set foreign_key_checks = 1;

使用limit分页查询时,做delete操作,会导致丢失数据

1.2代码

 @test
    public void test() {
        list<testsoitem> items = testsoitemservice.list();
        //1.当前全量数据
        log.info("1.当前全部数据:{}", items);
        ipage<testsoitem> page = new page<>();
        page.setcurrent(1);
        page.setsize(2);
        //2.分页查第一页
        ipage<testsoitem> items1 = testsoitemservice.page(page);
        log.info("2.第一页:{}", json.tojsonstring(items1));
        //3.删除
        testsoitemservice.removebyid(items1.getrecords().get(1).getid());
        log.info("3.已删除id:{}", items1.getrecords().get(1).getid());
        //4.add
        testsoitem addsoitem = new testsoitem();
        addsoitem.setlineno("00010");
        addsoitem.setsku("aa0793159");
        addsoitem.setqty(new bigdecimal(1));
        log.info("4:新增记录{}", json.tojsonstring(addsoitem));
        testsoitemservice.save(addsoitem);
        //5.分页查第2页
        page.setcurrent(2);
        ipage<testsoitem> items2 = testsoitemservice.page(page);
        log.info("5.第二页:{}", json.tojsonstring(items2));
    }

二、验证

1.验证前全部数据

<==    columns: id, line_no, sku, qty
<==        row: 1, 00020, a0001, 10.00
<==        row: 2, 00010, nt9531, 1.00
<==        row: 3, 00030, a0002, 2.00
<==        row: 4, 00040, a0003, 5.00
<==      total: 4
[testsoitem(lineno=00020, sku=a0001, qty=10.00), testsoitem(lineno=00010, sku=nt9531, qty=1.00), testsoitem(lineno=00030, sku=a0002, qty=2.00), testsoitem(lineno=00040, sku=a0003, qty=5.00)]

2.第一页数据

==>  preparing: select id,line_no,sku,qty from test_so_item limit ?,? 
==> parameters: 0(long), 2(long)
<==    columns: id, line_no, sku, qty
<==        row: 1, 00020, a0001, 10.00
<==        row: 2, 00010, nt9531, 1.00
{"current":1,"pages":2,"records":[{"id":1,"lineno":"00020","qty":10.00,"sku":"a0001"},{"id":2,"lineno":"00010","qty":1.00,"sku":"nt9531"}],"searchcount":true,"size":2,"total":4}

3.删除记录

==>  preparing: delete from test_so_item where id=? 
==> parameters: 2(long)
<==    updates: 1

4. 新增记录

==>  preparing: insert into test_so_item ( line_no, sku, qty ) values ( ?, ?, ? ) 
==> parameters: 00010(string), aa0793159(string), 1(bigdecimal)
<==    updates: 1
{"lineno":"00010","qty":1,"sku":"aa0793159"}

5.第二页数据

==>  preparing: select id,line_no,sku,qty from test_so_item limit ?,? 
==> parameters: 2(long), 2(long)
<==    columns: id, line_no, sku, qty
<==        row: 4, 00040, a0003, 5.00
<==        row: 5, 00010, aa0793159, 1.00
{"current":2,"pages":2,"records":[{"id":4,"lineno":"00040","qty":5.00,"sku":"a0003"},{"id":5,"lineno":"00010","qty":1.00,"sku":"aa0793159"}],"searchcount":true,"size":2,"total":4}

6. 验证后的数据

使用limit分页查询时,做delete操作,会导致丢失数据

7. log

sqlsession [org.apache.ibatis.session.defaults.defaultsqlsession@2e6bac5a] was not registered for synchronization because synchronization is not active
<==    columns: id, line_no, sku, qty
<==        row: 1, 00020, a0001, 10.00
<==        row: 2, 00010, nt9531, 1.00
<==        row: 3, 00030, a0002, 2.00
<==        row: 4, 00040, a0003, 5.00
<==      total: 4
closing non transactional sqlsession [org.apache.ibatis.session.defaults.defaultsqlsession@232438a8]
jdbc connection [com.alibaba.druid.proxy.jdbc.connectionproxyimpl@7ed49ba] will not be managed by spring
2020-04-15 17:17:56.950 - info 1412 --- [           main] - [] c.z.p.s.impl.testsoitemserviceimpltest   : 1.当前全部数据:[testsoitem(lineno=00020, sku=a0001, qty=10.00), testsoitem(lineno=00010, sku=nt9531, qty=1.00), testsoitem(lineno=00030, sku=a0002, qty=2.00), testsoitem(lineno=00040, sku=a0003, qty=5.00)]


jdbc connection [com.alibaba.druid.proxy.jdbc.connectionproxyimpl@2907d3e8] will not be managed by spring
 jsqlparsercountoptimize sql=select  id,line_no,sku,qty  from test_so_item
<==    updates: 1
closing non transactional sqlsession [org.apache.ibatis.session.defaults.defaultsqlsession@2e6bac5a]
==>  preparing: select count(1) from test_so_item 
==> parameters: 
<==    columns: count(1)
<==        row: 4
==>  preparing: select id,line_no,sku,qty from test_so_item limit ?,? 
==> parameters: 0(long), 2(long)
<==    columns: id, line_no, sku, qty
<==        row: 1, 00020, a0001, 10.00
<==        row: 2, 00010, nt9531, 1.00
<==      total: 2
closing non transactional sqlsession [org.apache.ibatis.session.defaults.defaultsqlsession@70382eb1]
2020-04-15 17:18:24.496 - info 1412 --- [           main] - [] c.z.p.s.impl.testsoitemserviceimpltest   : 2.第一页:{"current":1,"pages":2,"records":[{"id":1,"lineno":"00020","qty":10.00,"sku":"a0001"},{"id":2,"lineno":"00010","qty":1.00,"sku":"nt9531"}],"searchcount":true,"size":2,"total":4}
creating a new sqlsession
sqlsession [org.apache.ibatis.session.defaults.defaultsqlsession@d9420bf] was not registered for synchronization because synchronization is not active
jdbc connection [com.alibaba.druid.proxy.jdbc.connectionproxyimpl@2907d3e8] will not be managed by spring
==>  preparing: delete from test_so_item where id=? 
==> parameters: 2(long)
<==    updates: 1
closing non transactional sqlsession [org.apache.ibatis.session.defaults.defaultsqlsession@d9420bf]
2020-04-15 17:18:24.597 - info 1412 --- [           main] - [] c.z.p.s.impl.testsoitemserviceimpltest   : 3.已删除id:2
2020-04-15 17:18:24.598 - info 1412 --- [           main] - [] c.z.p.s.impl.testsoitemserviceimpltest   : 4:新增记录{"lineno":"00010","qty":1,"sku":"aa0793159"}
creating a new sqlsession
sqlsession [org.apache.ibatis.session.defaults.defaultsqlsession@1145d71f] was not registered for synchronization because synchronization is not active
jdbc connection [com.alibaba.druid.proxy.jdbc.connectionproxyimpl@2907d3e8] will not be managed by spring
==>  preparing: insert into test_so_item ( line_no, sku, qty ) values ( ?, ?, ? ) 
==> parameters: 00010(string), aa0793159(string), 1(bigdecimal)
<==    updates: 1
closing non transactional sqlsession [org.apache.ibatis.session.defaults.defaultsqlsession@1145d71f]
creating a new sqlsession
sqlsession [org.apache.ibatis.session.defaults.defaultsqlsession@64aa7a33] was not registered for synchronization because synchronization is not active
jdbc connection [com.alibaba.druid.proxy.jdbc.connectionproxyimpl@2907d3e8] will not be managed by spring
 jsqlparsercountoptimize sql=select  id,line_no,sku,qty  from test_so_item
==>  preparing: select count(1) from test_so_item 
==> parameters: 
<==    columns: count(1)
<==        row: 4
==>  preparing: select id,line_no,sku,qty from test_so_item limit ?,? 
==> parameters: 2(long), 2(long)
<==    columns: id, line_no, sku, qty
<==        row: 4, 00040, a0003, 5.00
<==        row: 5, 00010, aa0793159, 1.00
<==      total: 2
closing non transactional sqlsession [org.apache.ibatis.session.defaults.defaultsqlsession@64aa7a33]
2020-04-15 17:18:24.721 - info 1412 --- [           main] - [] c.z.p.s.impl.testsoitemserviceimpltest   : 5.第二页:{"current":2,"pages":2,"records":[{"id":4,"lineno":"00040","qty":5.00,"sku":"a0003"},{"id":5,"lineno":"00010","qty":1.00,"sku":"aa0793159"}],"searchcount":true,"size":2,"total":4}
2020-04-15 17:18:24.735 - warn 1412 --- [      thread-31] - [] o.s.cloud.stream.binding.bindingservice  : trying to unbind 'sappurchaseorder-input', but no binding found.
2020-04-15 17:18:24.736 - info 1412 --- [      thread-31] - [] o.s.i.endpoint.eventdrivenconsumer       : removing {logging-channel-adapter:_org.springframework.integration.errorlogger} as a subscriber to the 'errorchannel' channel
2020-04-15 17:18:24.736 - info 1412 --- [      thread-31] - [] o.s.i.channel.publishsubscribechannel    : channel '{server.name}-1.errorchannel' has 0 subscriber(s).
2020-04-15 17:18:24.736 - info 1412 --- [      thread-31] - [] o.s.i.endpoint.eventdrivenconsumer       : stopped _org.springframework.integration.errorlogger
2020-04-15 17:18:24.767 - warn 1412 --- [      thread-32] - [] o.s.c.support.defaultlifecycleprocessor  : failed to stop bean 'inputbindinglifecycle'

三、结论

在使用limit分页查询时,做delete操作,会导致丢失数据。如案例中id=3 的记录,在第一、二页均没有查到。

使用limit分页查询时,做delete操作,会导致丢失数据

使用limit分页查询时,做delete操作,会导致丢失数据

四、建议

使用limit分页查询的问题:

  1. 数据量比较大时,页数越大,查询性能越差。

    原因参考文章:

  2. 查询时使用delete 进行物理删除时,会导致漏查询数据(同时更不建议使用物理删除,尽量使用逻辑删除)。