MySql 全文检索后查询锁

Question

• MySQL 引擎:InnoDB
• MySql版本:5.6.31

1.运行第一个简单查询

SELECT o.id
FROM tbl_oper o
WHERE o.id_oper_tip = 8 AND o.id_oper_naim = 6;

结果:7 行

2.在同一个表上运行全文搜索

SELECT
    o.id,
    MATCH(o.full_text_search) AGAINST('7930' IN BOOLEAN MODE) 'match'
FROM tbl_oper o
WHERE 0 < MATCH(o.full_text_search) AGAINST('7930' IN BOOLEAN MODE)

结果:6行(无论)

3.重复第一个查询，结果:0行，但全文搜索仍返回6行

4.没有“AND”关键字的查询正常工作:

SELECT o.id
FROM tbl_oper o
WHERE o.id_oper_tip = 8

7行

SELECT o.id
FROM tbl_oper o
WHERE o.id_oper_naim = 6

500 多行

5.第一个查询仅在重新启动后才有效。为什么？

从 IDE、shell、php-app 运行查询时的结果相同

为 tbl_oper 创建语句:

CREATE TABLE 'tbl_oper' (
    'id'                INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    'id_oper_naim'      INT(10) UNSIGNED          DEFAULT NULL,
    'id_oper_tip'       INT(10) UNSIGNED          DEFAULT NULL,
    'num'               VARCHAR(20)               DEFAULT NULL,
    'data_oper'         DATE                      DEFAULT NULL,
    'time_oper'         VARCHAR(20)               DEFAULT NULL,
    'id_sotrZ'          INT(10) UNSIGNED          DEFAULT NULL,
    'id_fiz_deb'        INT(10) UNSIGNED          DEFAULT NULL,
    'id_yur_deb'        INT(10) UNSIGNED          DEFAULT NULL,
    'id_podrazdel_deb'  INT(10) UNSIGNED          DEFAULT NULL,
    'id_fiz_kred'       INT(10) UNSIGNED          DEFAULT NULL,
    'id_yur_kred'       INT(10) UNSIGNED          DEFAULT NULL,
    'id_podrazdel_kred' INT(10) UNSIGNED          DEFAULT NULL,
    'id_nds_stavka'     INT(10) UNSIGNED          DEFAULT NULL,
    'summa'             DECIMAL(10, 2)            DEFAULT NULL,
    'summa_nds'         FLOAT                     DEFAULT NULL,
    'comment'           TEXT,
    'prov'              INT(10) UNSIGNED          DEFAULT NULL,
    'num_kred'          VARCHAR(20)               DEFAULT NULL,
    'prem'              INT(11)                   DEFAULT NULL,
    'id_dealer'         INT(10) UNSIGNED          DEFAULT NULL,
    'full_text_search'  TEXT,
    PRIMARY KEY ('id'),
    KEY 'id_podrazdel_kred' ('id_podrazdel_kred'),
    KEY 'id_oper_naim' ('id_oper_naim'),
    KEY 'id_oper_tip' ('id_oper_tip'),
    KEY 'id_yur_deb' ('id_yur_deb'),
    KEY 'id_podrazdel_deb' ('id_podrazdel_deb'),
    KEY 'id_yur_kred' ('id_yur_kred'),
    KEY 'id_fiz_deb' ('id_fiz_deb'),
    KEY 'id_nds_stavka' ('id_nds_stavka'),
    KEY 'ct_oper_sotrz_idx' ('id_sotrZ'),
    KEY 'ct_oper_fiz_kred_idx' ('id_fiz_kred'),
    FULLTEXT KEY 'full_text_search' ('full_text_search'),
    CONSTRAINT 'ct_oper_fiz_deb' FOREIGN KEY ('id_fiz_deb') REFERENCES 'tbl_person' ('id') ON UPDATE CASCADE,
    CONSTRAINT 'ct_oper_fiz_kred' FOREIGN KEY ('id_fiz_kred') REFERENCES 'tbl_person' ('id') ON UPDATE CASCADE,
    CONSTRAINT 'ct_oper_naim' FOREIGN KEY ('id_oper_naim') REFERENCES 'spr_oper_naim' ('id') ON UPDATE CASCADE,
    CONSTRAINT 'ct_oper_sotrz' FOREIGN KEY ('id_sotrZ') REFERENCES 'tbl_person' ('id') ON UPDATE CASCADE,
    CONSTRAINT 'ct_oper_type' FOREIGN KEY ('id_oper_tip') REFERENCES 'spr_oper_tip' ('id') ON UPDATE CASCADE,
    CONSTRAINT 'ct_podrazdel_deb' FOREIGN KEY ('id_podrazdel_deb') REFERENCES 'tbl_podrazdel' ('id') ON UPDATE CASCADE,
    CONSTRAINT 'ct_podrazdel_kred' FOREIGN KEY ('id_podrazdel_kred') REFERENCES 'tbl_podrazdel' ('id') ON UPDATE CASCADE,
    CONSTRAINT 'ct_yur_deb' FOREIGN KEY ('id_yur_deb') REFERENCES 'tbl_yur' ('id') ON UPDATE CASCADE,
    CONSTRAINT 'ct_yur_kred' FOREIGN KEY ('id_yur_kred') REFERENCES 'tbl_yur' ('id') ON UPDATE CASCADE
)
    ENGINE = InnoDB
    AUTO_INCREMENT = 38357
    DEFAULT CHARSET = utf8

Answer 1

您尝试过修复表吗？

另请参阅: https://dev.mysql.com/doc/refman/5.6/en/rebuilding-tables.html