技术领域technical field
本发明涉及数据库技术领域，特别是涉及基于逻辑删除的SQL(Structured QueryLanguage，结构化查询语言，简称SQL)执行方法、装置、计算机设备和存储介质。The present invention relates to the technical field of databases, in particular to a tombstone-based SQL (Structured Query Language, SQL for short) execution method, device, computer equipment and storage medium.
背景技术Background technique
逻辑删除指的是，不真正的在数据库上执行delete操作删除数据,而是把数据的某个字段置为一个标记。同时，在数据库表里面针对该数据设置一个用于表示逻辑删除的字段(例如is_delete),如果该字段为1则表示数据被逻辑删除,如果该字段为0则表示数据未被逻辑删除。Logical deletion refers to not actually performing a delete operation on the database to delete data, but to set a certain field of the data as a mark. At the same time, set a field (such as is_delete) for the data in the database table to indicate logical deletion. If the field is 1, it means that the data is logically deleted, and if the field is 0, it means that the data is not logically deleted.
在实现本发明的过程中，发明人发现现有技术中存在如下问题：在数据被用户逻辑删除之后，使用传统的SQL语句，将难以排除该数据，导致SQL执行结果无法满足要求。In the process of realizing the present invention, the inventor found the following problem in the prior art: after the data is logically deleted by the user, it will be difficult to exclude the data by using the traditional SQL statement, resulting in the SQL execution result failing to meet the requirements.
发明内容Contents of the invention
基于此，有必要针对现有方式对逻辑删除的SQL执行结果不准确的问题，提供一种对逻辑删除的SQL执行方法、装置、计算机设备和存储介质。Based on this, it is necessary to provide an SQL execution method, device, computer equipment and storage medium for tombstones to address the problem of inaccurate SQL execution results for tombstones in existing methods.
本发明实施例提供的方案包括：The scheme that the embodiment of the present invention provides includes:
一种对逻辑删除的SQL执行方法，包括：A SQL execution method for tombstone, including:
获取待执行SQL语句，识别所述待执行SQL语句的操作类型；Acquire the SQL statement to be executed, and identify the operation type of the SQL statement to be executed;
当所述操作类型为删除、更新或者查询时，根据实体类中预设的逻辑删除标志，识别待执行SQL语句针对的数据表中的逻辑删除字段；所述实体类与所述数据表之间有映射关系；When the operation type is delete, update or query, according to the preset tombstone flag in the entity class, identify the tombstone field in the data table targeted by the SQL statement to be executed; between the entity class and the data table There is a mapping relationship;
根据所述逻辑删除字段生成对应的SQL操作条件，根据所述SQL操作条件更新所述待执行SQL语句，执行更新得到的SQL语句。Generate a corresponding SQL operation condition according to the tombstone field, update the SQL statement to be executed according to the SQL operation condition, and execute the updated SQL statement.
在其中一个实施例中，根据实体类中预设的逻辑删除标志，识别待执行SQL语句针对的数据表中的逻辑删除字段的步骤包括：In one of the embodiments, according to the preset tombstone flag in the entity class, the step of identifying the tombstone field in the data table targeted by the SQL statement to be executed includes:
确定所述待执行SQL语句对应的实体类；Determine the entity class corresponding to the SQL statement to be executed;
检测所述实体类中预设的逻辑删除标志，根据逻辑删除标志识别数据表中的逻辑删除字段。Detecting the preset tombstone flag in the entity class, and identifying the tombstone field in the data table according to the tombstone flag.
在其中一个实施例中，还包括：In one of the embodiments, it also includes:
若检测到所述实体类中不包含预设的逻辑删除标志，执行所述待执行SQL语句；If it is detected that the entity class does not contain a preset tombstone flag, execute the SQL statement to be executed;
和/或，and / or,
在确定所述待执行SQL语句的操作类型的步骤之后，还包括：After the step of determining the operation type of the SQL statement to be executed, it also includes:
当待执行SQL语句的操作类型为新增操作类型时，执行所述待执行SQL语句。When the operation type of the SQL statement to be executed is a new operation type, execute the SQL statement to be executed.
在其中一个实施例中，确定所述待执行SQL语句对应的实体类的步骤包括：In one of the embodiments, the step of determining the entity class corresponding to the SQL statement to be executed includes:
根据预设的SQL解析器对所述待执行SQL语句进行解析,得到所述待执行SQL语句对应的语法树；遍历所述语法树的FROM节点和/或FROM节点的叶子节点，确定待执行SQL语句针对的数据表；Analyze the SQL statement to be executed according to a preset SQL parser to obtain a syntax tree corresponding to the SQL statement to be executed; traverse the FROM node and/or leaf nodes of the FROM node of the syntax tree to determine the SQL statement to be executed The data table targeted by the statement;
根据实体类与数据表的映射关系，得到所述数据表对应的实体类，确定为所述待执行SQL语句对应的实体类。According to the mapping relationship between the entity class and the data table, the entity class corresponding to the data table is obtained, and determined as the entity class corresponding to the SQL statement to be executed.
在其中一个实施例中，根据逻辑删除标志识别所述数据表中的逻辑删除字段的步骤包括：In one of the embodiments, the step of identifying the tombstone field in the data table according to the tombstone flag includes:
确定实体类中逻辑删除标志所指向的属性，根据实体类中属性与数据表中字段的映射关系，得到所述属性对应的字段，作为逻辑删除字段。Determine the attribute pointed to by the tombstone flag in the entity class, and obtain the field corresponding to the attribute as the tombstone field according to the mapping relationship between the attribute in the entity class and the field in the data table.
在其中一个实施例中，根据所述逻辑删除字段生成对应的SQL操作条件，根据所述SQL操作条件更新所述待执行SQL语句的步骤包括：In one of the embodiments, a corresponding SQL operation condition is generated according to the tombstone field, and the step of updating the SQL statement to be executed according to the SQL operation condition includes:
若所述操作类型为更新或者查询，则生成X.is_delete＝K1的第一操作条件，在所述待执行SQL语句的where属性中增加所述第一操作条件；If the operation type is update or query, then generate the first operation condition of X.is_delete=K1, and increase the first operation condition in the where attribute of the SQL statement to be executed;
若所述操作类型为删除，则生成X.is_delete＝K2的第二操作条件，根据所述待执行SQL语句创建一个更新类型的SQL语句，在所述更新类型的SQL语句的set属性中增加所述第二操作条件；If the operation type is deletion, then generate the second operation condition of X.is_delete=K2, create an update type SQL statement according to the SQL statement to be executed, and increase the set attribute of the update type SQL statement the second operating condition;
其中，X表示待执行SQL语句指向的数据表中的数据项，is_delete表示逻辑删除字段，K1、K2表示预设的逻辑值，当X.is_delete＝K1时，表示数据X未被逻辑删除，当X.is_delete＝K2时，表示数据X被逻辑删除。Among them, X represents the data item in the data table pointed to by the SQL statement to be executed, is_delete represents the tombstone field, and K1 and K2 represent preset logical values. When X.is_delete=K1, it means that the data X has not been tombstoned. When X.is_delete=K2, it means that data X is logically deleted.
在其中一个实施例中，还包括：In one of the embodiments, it also includes:
创建数据表对应的实体类，在所述实体类中设置用于表示逻辑删除的属性，为该属性设置对应的逻辑删除标志。An entity class corresponding to the data table is created, an attribute representing tombstone is set in the entity class, and a corresponding tombstone flag is set for the attribute.
在其中一个实施例中，在所述实体类中设置用于表示逻辑删除的属性，为该属性设置对应的逻辑删除标志的步骤包括：In one of the embodiments, an attribute for representing tombstone is set in the entity class, and the step of setting a corresponding tombstone flag for the attribute includes:
在所述实体类中设置Boolean类型的、用于表示逻辑删除的属性，为该属设置逻辑删除标志@LogicDelete。An attribute of Boolean type used to represent logical deletion is set in the entity class, and a logical deletion flag @LogicDelete is set for this attribute.
一种对逻辑删除的SQL执行装置，包括：An SQL execution device for tombstone, comprising:
类型识别模块，用于获取待执行SQL语句，识别所述待执行SQL语句的操作类型；A type identification module, configured to obtain the SQL statement to be executed, and identify the operation type of the SQL statement to be executed;
字段识别模块，用于根据实体类中预设的逻辑删除标志，识别待执行SQL语句针对的数据表中的逻辑删除字段；所述实体类与所述数据表之间有映射关系；The field identification module is used to identify the tombstone field in the data table targeted by the SQL statement to be executed according to the preset tombstone mark in the entity class; there is a mapping relationship between the entity class and the data table;
以及，修改执行模块，用于根据所述逻辑删除字段生成对应的SQL操作条件，根据所述SQL操作条件更新所述待执行SQL语句，执行更新得到的SQL语句。And, the execution module is modified, configured to generate a corresponding SQL operation condition according to the tombstone field, update the SQL statement to be executed according to the SQL operation condition, and execute the updated SQL statement.
在其中一个实施例中，所述字段识别模块包括：In one of the embodiments, the field identification module includes:
类识别单元，用于当所述操作类型为删除、更新或者查询时，确定所述待执行SQL语句对应的实体类；A class identification unit, configured to determine the entity class corresponding to the SQL statement to be executed when the operation type is delete, update or query;
字段识别单元，用于检测所述实体类中预设的逻辑删除标志，根据逻辑删除标志识别数据表中的逻辑删除字段。The field identifying unit is configured to detect a preset tombstone flag in the entity class, and identify a tombstone field in the data table according to the tombstone flag.
在其中一个实施例中，还包括：In one of the embodiments, it also includes:
第一执行模块，用于若检测到所述实体类中不包含预设的逻辑删除标志，执行所述待执行SQL语句；The first execution module is used to execute the SQL statement to be executed if it is detected that the entity class does not contain a preset tombstone flag;
和/或，and / or,
第二执行模块，用于当确定出所述待执行SQL语句的操作类型为新增操作类型时，执行所述待执行SQL语句。The second execution module is configured to execute the SQL statement to be executed when it is determined that the operation type of the SQL statement to be executed is a newly added operation type.
在其中一个实施例中，所述类识别单元包括：In one of the embodiments, the class identification unit includes:
数据表识别子单元，用于根据预设的SQL解析器对所述待执行SQL语句进行解析,得到所述待执行SQL语句对应的语法树；遍历所述语法树的FROM节点和/或FROM节点的叶子节点，确定待执行SQL语句针对的数据表；The data table identification subunit is used to analyze the SQL statement to be executed according to a preset SQL parser to obtain the syntax tree corresponding to the SQL statement to be executed; traverse the FROM node and/or FROM node of the syntax tree The leaf node of is to determine the data table targeted by the SQL statement to be executed;
类识别子单元，用于根据实体类与数据表的映射关系，得到所述数据表对应的实体类，确定为所述待执行SQL语句对应的实体类。The class identification subunit is configured to obtain the entity class corresponding to the data table according to the mapping relationship between the entity class and the data table, and determine it as the entity class corresponding to the SQL statement to be executed.
在其中一个实施例中，所述字段识别单元包括：In one of the embodiments, the field identification unit includes:
字段确定子单元，用于确定实体类中逻辑删除标志所指向的属性，根据实体类中属性与数据表中字段的映射关系，得到所述属性对应的字段，作为逻辑删除字段。The field determining subunit is used to determine the attribute pointed to by the tombstone flag in the entity class, and obtain the field corresponding to the attribute as the tombstone field according to the mapping relationship between the attribute in the entity class and the field in the data table.
在其中一个实施例中，所述修改执行模块包括：In one of the embodiments, the modification execution module includes:
第一修改单元，用于若所述操作类型为更新或者查询，则生成X.is_delete＝K1的第一操作条件，在所述待执行SQL语句的where属性中增加所述第一操作条件；The first modification unit is used to generate a first operation condition of X.is_delete=K1 if the operation type is update or query, and add the first operation condition to the where attribute of the SQL statement to be executed;
第二修改单元，用于若所述操作类型为删除，则生成X.is_delete＝K2的第二操作条件，根据所述待执行SQL语句创建一个更新类型的SQL语句，在所述更新类型的SQL语句的set属性中增加所述第二操作条件；The second modification unit is used to generate a second operating condition of X.is_delete=K2 if the operation type is deletion, and create an update-type SQL statement according to the SQL statement to be executed, in the update-type SQL statement Adding the second operation condition in the set attribute of the statement;
其中，X表示待执行SQL语句指向的数据表中的数据项，is_delete表示逻辑删除字段，K1、K2表示预设的逻辑值，当X.is_delete＝K1时，表示数据X未被逻辑删除，当X.is_delete＝K2时，表示数据X被逻辑删除。Among them, X represents the data item in the data table pointed to by the SQL statement to be executed, is_delete represents the tombstone field, and K1 and K2 represent preset logical values. When X.is_delete=K1, it means that the data X has not been tombstoned. When X.is_delete=K2, it means that data X is logically deleted.
上述对逻辑删除的SQL执行方法及装置,在获取到待执行SQL语句之后，识别所述待执行SQL语句的操作类型；当所述操作类型为删除、更新或者查询时，根据实体类中预设的逻辑删除标志识别数据表中的逻辑删除字段；根据所述逻辑删除字段生成对应的SQL操作条件，根据生成的SQL操作条件更新待执行SQL语句，进而执行更新得到的SQL语句。由此实现了自动对待执行SQL进行转换,能适应所有的SQL语句。用户在编辑SQL语句的时候，无需关注数据库中的数据是否被逻辑删除，通过常规的SQL语句，便可实现对逻辑删除的数据的SQL操作，既简化了用户的SQL操作难度，又提高的SQL操作结果的准确度。The above-mentioned SQL execution method and device for tombstone, after obtaining the SQL statement to be executed, identify the operation type of the SQL statement to be executed; when the operation type is delete, update or query, according to the preset in the entity class The tombstone flag identifies the tombstone field in the data table; generates the corresponding SQL operation condition according to the tombstone field, updates the SQL statement to be executed according to the generated SQL operation condition, and then executes the updated SQL statement. In this way, the SQL to be executed is automatically converted, and it can adapt to all SQL statements. When users edit SQL statements, they don’t need to pay attention to whether the data in the database is logically deleted. Through regular SQL statements, SQL operations on logically deleted data can be realized, which not only simplifies the difficulty of SQL operations for users, but also improves SQL The accuracy of the result of the operation.
一种计算机设备，包括存储器、处理器以及存储在所述存储器上并可在所述处理器上运行的计算机程序，所述处理器执行所述计算机程序时实现如上述对逻辑删除的SQL执行方法。A computer device, comprising a memory, a processor, and a computer program stored on the memory and operable on the processor, when the processor executes the computer program, the above SQL execution method for tombstone is implemented .
上述计算机设备，通过所述处理器上运行的计算机程序，实现了通过常规的SQL语句，便可实现对逻辑删除的数据的SQL操作，既简化了用户的SQL操作难度，又提高的SQL操作结果的准确度。The above-mentioned computer equipment, through the computer program running on the processor, realizes the SQL operation on the logically deleted data through the conventional SQL statement, which not only simplifies the SQL operation difficulty of the user, but also improves the SQL operation result. the accuracy.
一种计算机存储介质，其上存储有计算机程序，该程序被处理器执行时实现如上述对逻辑删除的SQL执行方法。A computer storage medium, on which a computer program is stored, and when the program is executed by a processor, the above SQL execution method for tombstone is realized.
上述计算机存储介质，通过其存储的计算机程序，实现了通过常规的SQL语句，便可实现对逻辑删除的数据的SQL操作，既简化了用户的SQL操作难度，又提高的SQL操作结果的准确度。The above-mentioned computer storage medium, through its stored computer program, realizes the SQL operation on logically deleted data through conventional SQL statements, which not only simplifies the difficulty of SQL operation for users, but also improves the accuracy of SQL operation results .
附图说明Description of drawings
图1为一个实施例中对逻辑删除的SQL执行方法的应用环境图；Fig. 1 is an application environment diagram to the SQL execution method of tombstone in one embodiment;
图2为一实施例的对逻辑删除的SQL执行方法的示意性流程图；Fig. 2 is a schematic flow chart of the SQL execution method to tombstone of an embodiment;
图3为一实施例的查询SQL语句对应的语法树的示意图；Fig. 3 is a schematic diagram of a syntax tree corresponding to the query SQL statement of an embodiment;
图4为一实施例的删除SQL语句对应的语法树的示意图；Fig. 4 is a schematic diagram of a syntax tree corresponding to the delete SQL statement of an embodiment;
图5为另一实施例的对逻辑删除的SQL执行方法的示意性流程图Fig. 5 is a schematic flowchart of another embodiment of the SQL execution method for tombstone
图6为一实施例的对逻辑删除的SQL执行装置的示意性结构图。Fig. 6 is a schematic structural diagram of an SQL execution device for tombstone deletion according to an embodiment.
具体实施方式Detailed ways
为了使本发明的目的、技术方案及优点更加清楚明白，以下结合附图及实施例，对本发明进行进一步详细说明。应当理解，此处所描述的具体实施例仅仅用以解释本发明，并不用于限定本发明。In order to make the object, technical solution and advantages of the present invention more clear, the present invention will be further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described here are only used to explain the present invention, not to limit the present invention.
在本文中提及“实施例”意味着，结合实施例描述的特定特征、结构或特性可以包含在本申请的至少一个实施例中。在说明书中的各个位置出现该短语并不一定均是指相同的实施例，也不是与其它实施例互斥的独立的或备选的实施例。本领域技术人员显式地和隐式地理解的是，本文所描述的实施例可以与其它实施例相结合。Reference herein to an "embodiment" means that a particular feature, structure, or characteristic described in connection with the embodiment can be included in at least one embodiment of the present application. The occurrences of this phrase in various places in the specification are not necessarily all referring to the same embodiment, nor are separate or alternative embodiments mutually exclusive of other embodiments. It is understood explicitly and implicitly by those skilled in the art that the embodiments described herein can be combined with other embodiments.
结构化查询语言(简称SQL)，是一种重要的关系数据库操作语言，用于存取数据以及查询、更新和管理关系数据库；它允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以具有完全不同底层结构的不同数据库系统,可以使用相同的结构化查询语言作为数据输入与管理的接口。结构化查询语言语句可以嵌套，这使它具有极大的灵活性和强大的功能。Structured Query Language (SQL for short) is an important relational database operating language for accessing data and querying, updating and managing relational databases; it allows users to work on high-level data structures. It does not require the user to specify the data storage method, nor does it require the user to understand the specific data storage method, so different database systems with completely different underlying structures can use the same structured query language as the interface for data input and management. Structured query language statements can be nested, which makes it extremely flexible and powerful.
本申请提供的对逻辑删除的SQL执行方法，可以应用于如图1所示的计算机设备中。该计算机设备可以是终端，包括但不限于是各种个人计算机、笔记本电脑、智能手机、平板电脑和便携式可穿戴设备。The SQL execution method for tombstone provided by the present application can be applied to the computer device shown in FIG. 1 . The computer equipment may be a terminal, including but not limited to various personal computers, notebook computers, smart phones, tablet computers and portable wearable devices.
在一实施例中，该计算机设备的内部结构图可以如图1所示。该计算机设备包括通过系统总线连接的处理器、存储器、显示屏和输入装置。其中，该计算机设备的处理器用于提供计算和控制能力。该计算机设备的存储器包括非易失性存储介质、内存储器。该非易失性存储介质存储有操作系统和计算机程序。该内存储器为非易失性存储介质中的操作系统和计算机程序的运行提供环境。该计算机程序被处理器执行时以实现一种对逻辑删除的SQL执行方法。该计算机设备的显示屏可以是液晶显示屏或者电子墨水显示屏，该计算机设备的输入装置可以是显示屏上覆盖的触摸层，也可以是计算机设备外壳上设置的按键、轨迹球或触控板，还可以是外接的键盘、触控板或鼠标等。In an embodiment, the internal structure diagram of the computer device may be as shown in FIG. 1 . The computer device includes a processor, a memory, a display screen and an input device connected by a system bus. Wherein, the processor of the computer device is used to provide calculation and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system and computer programs. The internal memory provides an environment for the operation of the operating system and computer programs in the non-volatile storage medium. When the computer program is executed by the processor, a SQL execution method for tombstone is realized. The display screen of the computer device may be a liquid crystal display screen or an electronic ink display screen, and the input device of the computer device may be a touch layer covered on the display screen, or a button, a trackball or a touch pad provided on the casing of the computer device , and can also be an external keyboard, touchpad, or mouse.
本领域技术人员可以理解，图1中示出的结构，仅仅是与本申请方案相关的部分结构的框图，并不构成对本申请方案所应用于其上的计算机设备的限定，具体的计算机设备可以包括比图中所示更多或更少的部件，或者组合某些部件，或者具有不同的部件布置。Those skilled in the art can understand that the structure shown in Figure 1 is only a block diagram of a partial structure related to the solution of the present application, and does not constitute a limitation to the computer equipment on which the solution of the application is applied. The specific computer equipment can be More or fewer components than shown in the figures may be included, or some components may be combined, or have a different arrangement of components.
在一个实施例中，如图2所示，提供了一种对逻辑删除的SQL执行方法，包括以下步骤：In one embodiment, as shown in Figure 2, a kind of SQL execution method to tombstone is provided, comprising the following steps:
S11，获取待执行SQL语句，识别所述待执行SQL语句的操作类型。S11. Obtain an SQL statement to be executed, and identify an operation type of the SQL statement to be executed.
常见SQL语句的类型包含：查询类型、添加类型、修改类型和删除类型，其中，查询类型对应的语句也称作数据查询语言(DQL:Data Query Language)，添加类型、修改类型和删除类型对应的语句也称作数据操作语言(DML：Data Manipulation Language)。The types of common SQL statements include: query type, add type, modify type, and delete type. Among them, the statement corresponding to the query type is also called Data Query Language (DQL: Data Query Language), and the corresponding type of add type, modify type, and delete type Statements are also called Data Manipulation Language (DML: Data Manipulation Language).
对于查询类型，其语句也称为“数据检索语句”，用以从数据表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是用得最多的动词，其他常用的保留字包括WHERE、ORDER BY、GROUP BY以及HAVING等。这些保留字常与其他类型的SQL语句一起使用。添加类型、修改类型和删除类型对应的语句包括动词分别为INSERT，UPDATE和DELETE，它们分别用于添加、修改和删除数据表中的记录。For the query type, its statement is also called "data retrieval statement", which is used to obtain data from the data table and determine how the data is given in the application program. The reserved word SELECT is the most frequently used verb, and other commonly used reserved words include WHERE, ORDER BY, GROUP BY, and HAVING. These reserved words are often used with other types of SQL statements. The statements corresponding to adding type, modifying type and deleting type include verbs respectively INSERT, UPDATE and DELETE, which are respectively used to add, modify and delete records in the data table.
S12，当所述操作类型为删除、更新或者查询时，根据实体类中预设的逻辑删除标志，识别待执行SQL语句针对的数据表中的逻辑删除字段；所述实体类与所述数据表之间有映射关系。S12, when the operation type is delete, update or query, identify the tombstone field in the data table targeted by the SQL statement to be executed according to the preset tombstone flag in the entity class; the entity class and the data table There is a mapping relationship between them.
所述实体类与数据库中的数据表对应，基于目前主流的持久层(也就是数据访问层)的框架，在数据库建了一个数据表,则需要按照规范在代码里面创建一个对应的实体类，并且实体类里面的属性映射着数据表的字段。The entity class corresponds to the data table in the database. Based on the framework of the current mainstream persistence layer (that is, the data access layer), if a data table is built in the database, a corresponding entity class needs to be created in the code according to the specification. And the attributes in the entity class are mapped to the fields of the data table.
通常SQL语句所针对的对象是数据表中的数据项，例如：SELECT name,countryFROM Websites，该SQL语句指的是从"Websites"表中选取"name"和"country"数据项，即该SQL语句针对的是数据表"Websites"。由于数据表与实体类之间存在映射关系，由此，每个SQL语句都有其对应的实体类。Usually the object of the SQL statement is the data item in the data table, for example: SELECT name, countryFROM Websites, the SQL statement refers to selecting the "name" and "country" data items from the "Websites" table, that is, the SQL statement For the data table "Websites". Since there is a mapping relationship between data tables and entity classes, each SQL statement has its corresponding entity class.
在一实施例中，该步骤的实现包括：当所述操作类型为删除、更新或者查询时，确定所述待执行SQL语句对应的实体类。检测所述实体类中预设的逻辑删除标志，根据逻辑删除标志识别数据表中的逻辑删除字段。In an embodiment, the implementation of this step includes: when the operation type is delete, update or query, determining the entity class corresponding to the SQL statement to be executed. Detecting the preset tombstone flag in the entity class, and identifying the tombstone field in the data table according to the tombstone flag.
其中，所述逻辑删除标志是能够被执行SQL的程序识别，相当于对实体类中用于表示逻辑删除状态的属性的注解。由于实体类的属性与数据表的字段存在映射关系，因此由此可识别出数据表中用于记录逻辑删除信息的字段。Wherein, the tombstone flag can be recognized by a program that executes SQL, which is equivalent to a comment on an attribute in an entity class used to indicate a state of tombstone. Since there is a mapping relationship between the attributes of the entity class and the fields of the data table, the fields used to record the tombstone information in the data table can be identified.
S13，根据所述逻辑删除字段生成对应的SQL操作条件，根据所述SQL操作条件更新所述待执行SQL语句，执行更新得到的SQL语句。S13. Generate a corresponding SQL operation condition according to the tombstone field, update the SQL statement to be executed according to the SQL operation condition, and execute the updated SQL statement.
在一实施例中，逻辑删除字段对应的值为逻辑值，根据逻辑删除字段生成的SQL操作条件包括两类，一类为对应数据项被逻辑删除的条件，一类为对应数据项未被逻辑删除的条件。根据生成的SQL操作条件修改待执行SQL语句，即是在原来待执行SQL语句中增加对相关数据项的逻辑删除信息的考虑，通过执行更新后的SQL语句，能够得到更准确的数据结果。In one embodiment, the value corresponding to the tombstone field is a logical value, and the SQL operation conditions generated according to the tombstone field include two types, one is the condition that the corresponding data item is logically deleted, and the other is the condition that the corresponding data item is not logically deleted. The condition to delete. Modifying the SQL statement to be executed according to the generated SQL operation conditions is to add the consideration of logical deletion information of related data items to the original SQL statement to be executed. By executing the updated SQL statement, more accurate data results can be obtained.
可以理解的是，逻辑删除字段对应的值还可以为其他数据类型。It can be understood that the value corresponding to the tombstone field may also be other data types.
通过上述实施例的SQL执行方法，用户在编辑SQL语句的时候，无需关注数据库中的数据是否被逻辑删除，通过常规的SQL语句，便可实现对逻辑删除的数据的SQL操作，既简化了用户的SQL操作难度，又提高的SQL操作结果的准确度。Through the SQL execution method of the above-mentioned embodiment, the user does not need to pay attention to whether the data in the database is logically deleted when editing the SQL statement, and the SQL operation on the logically deleted data can be realized through the conventional SQL statement, which not only simplifies the user The difficulty of SQL operation is improved, and the accuracy of SQL operation results is improved.
在其中一个实施例中，本申请的SQL执行方法还包括：创建数据表对应的实体类，在所述实体类中设置用于表示逻辑删除的属性，并且为该属性设置对应的逻辑删除标志。In one embodiment, the SQL execution method of the present application further includes: creating an entity class corresponding to the data table, setting an attribute representing tombstone in the entity class, and setting a corresponding tombstone flag for the attribute.
通过逻辑删除标志，使得程序能够有效识别用于记录数据逻辑删除信息的逻辑删除字段，以此为基础修改待执行SQL语句。可以理解的，参考图5所示，在一个实施例中，若检测到所述实体类中不包含预设的逻辑删除标志，执行所述待执行SQL语句。此种情况下，不再对待执行SQL语句进行转换，及直接执行获取到的待执行SQL语句，不执行上述步骤S12以及步骤S13。Through the tombstone flag, the program can effectively identify the tombstone field used to record the tombstone information of the data, and modify the SQL statement to be executed based on this. It can be understood that referring to FIG. 5 , in one embodiment, if it is detected that the entity class does not contain a preset tombstone flag, the SQL statement to be executed is executed. In this case, the SQL statement to be executed is no longer converted, and the obtained SQL statement to be executed is directly executed, and the above steps S12 and S13 are not executed.
因为如果实体类中不包含预设的逻辑删除标志，程序将无法有效识别用于记录数据逻辑删除信息的逻辑删除字段，在此情况下，执行原来的待执行SQL语句，可防止逻辑删除字段识别出错导致SQL执行结果的偏差。Because if the entity class does not contain the preset tombstone flag, the program will not be able to effectively identify the tombstone field used to record the tombstone information of the data. In this case, executing the original SQL statement to be executed can prevent the tombstone field from being identified Mistakes lead to deviations in SQL execution results.
在一个实施例中，本申请的SQL执行方法还包括：在确定所述待执行SQL语句的操作类型的步骤之后，如果待执行SQL语句的操作类型为新增操作类型，则执行所述待执行SQL语句。即在此种情况下，不再对待执行SQL语句进行转换，及直接执行获取到的待执行SQL语句，不执行上述步骤S12以及步骤S13。In one embodiment, the SQL execution method of the present application further includes: after the step of determining the operation type of the SQL statement to be executed, if the operation type of the SQL statement to be executed is a newly added operation type, then execute the SQL statements. That is, in this case, the SQL statement to be executed is no longer converted, and the acquired SQL statement to be executed is directly executed, and the above step S12 and step S13 are not executed.
由于新增操作和数据的逻辑删除状态无关,因此不需要修改待执行的SQL，也不影响SQL执行结果的准确性。Since the new operation has nothing to do with the tombstone status of the data, there is no need to modify the SQL to be executed, nor does it affect the accuracy of the SQL execution results.
在其中一个实施例中，在所述实体类中设置Boolean类型的属性isDeleted，用于表示逻辑删除的属性；在属性isDeleted之前，设置逻辑删除标志。例如：In one of the embodiments, the attribute isDeleted of type Boolean is set in the entity class to represent the attribute of tombstone; before the attribute isDeleted, the flag of tombstone is set. E.g:
数据库有个数据表“t_user_info”为：The database has a data table "t_user_info" as:
对应地，需要在程序里面就创建一个对应的实体类,实体类的属性对应数据表的字段，该实体类可以为：Correspondingly, a corresponding entity class needs to be created in the program. The attributes of the entity class correspond to the fields of the data table. The entity class can be:
该实体类中的逻辑删除标志为@LogicDelete，其执行的属性是isDeleted,而属性isDeleted映射着数据表中的is_deleted字段，实现逻辑删除需要先知道逻辑删除字段,而@LogicDelete就是用来识别逻辑删除字段的。The logical deletion flag in this entity class is @LogicDelete, and the attribute it executes is isDeleted, and the attribute isDeleted maps the is_deleted field in the data table. To implement logical deletion, you need to know the logical deletion field first, and @LogicDelete is used to identify logical deletion field.
基于上述示例，在其中一个实施例中，根据逻辑删除标志识别所述数据表中的逻辑删除字段的步骤包括：确定实体类中逻辑删除标志所指向的属性，根据实体类中属性与数据表中字段的映射关系，得到所述属性对应的字段，作为逻辑删除字段。由此能够快速准确的确定出逻辑删除字段。Based on the above example, in one embodiment, the step of identifying the tombstone field in the data table according to the tombstone flag includes: determining the attribute pointed to by the tombstone flag in the entity class, and according to the attribute in the entity class and the data table The mapping relationship of the fields, the field corresponding to the attribute is obtained, and used as the tombstone field. Therefore, the tombstone field can be quickly and accurately determined.
在其中一个实施例中，确定所述待执行SQL语句对应的实体类的步骤包括：根据预设的SQL解析器对所述待执行SQL语句进行解析,得到所述待执行SQL语句对应的语法树；遍历所述语法树的FROM节点和/或FROM节点的叶子节点，确定待执行SQL语句针对的数据表；根据实体类与数据表的映射关系，得到所述数据表对应的实体类，确定为所述待执行SQL语句对应的实体类。In one of the embodiments, the step of determining the entity class corresponding to the SQL statement to be executed includes: parsing the SQL statement to be executed according to a preset SQL parser to obtain a syntax tree corresponding to the SQL statement to be executed Traverse the FROM node of the syntax tree and/or the leaf node of the FROM node to determine the data table targeted at the SQL statement to be executed; obtain the corresponding entity class of the data table according to the mapping relationship between the entity class and the data table, and determine as The entity class corresponding to the SQL statement to be executed.
例如：若待执行SQL语句是查询语句、删除语句或者更新语句,需要对待执行SQL语句进行分析,可以使用开源工具druid里面的SQL解析器对待执行SQL语句进行解析,解析工具能把SQL语句解析成一棵语法树。查询SQL语句分析出的结果是一个SelectStatement的对象,里面主要包含SelectList、From、Where属性，更新SQL语句分析出的结构是一个UpdateStatement的对象,里面主要包含了SetItem、From、Where属性；删除SQL语句分析出的结构是一个DeleteStatement的对象,里面主要包含了From、Where属性。查询SQL语句对应的语法树参见图3所示，更新SQL语句对应的语法树参见图4所示。For example: if the SQL statement to be executed is a query statement, delete statement or update statement, the SQL statement to be executed needs to be analyzed, and the SQL parser in the open source tool druid can be used to analyze the SQL statement to be executed. The analysis tool can parse the SQL statement into a a syntax tree. The result analyzed by the query SQL statement is a SelectStatement object, which mainly includes the SelectList, From, and Where attributes; the structure analyzed by the update SQL statement is an UpdateStatement object, which mainly includes the SetItem, From, and Where attributes; delete the SQL statement The analyzed structure is a DeleteStatement object, which mainly includes From and Where attributes. The syntax tree corresponding to the query SQL statement is shown in FIG. 3 , and the syntax tree corresponding to the update SQL statement is shown in FIG. 4 .
通过SQL语句对应的语法树，能够快速确定出待执行SQL语句对应的实体类。Through the syntax tree corresponding to the SQL statement, the entity class corresponding to the SQL statement to be executed can be quickly determined.
在一实施例中，可以在遍历语法树的过程中修改语法树对应节点的数据，从而达到修改待执行SQL的目的。In an embodiment, the data of the corresponding node of the syntax tree can be modified during the process of traversing the syntax tree, so as to achieve the purpose of modifying the SQL to be executed.
在其中一个实施例中，根据所述逻辑删除字段生成对应的SQL操作条件，根据所述SQL操作条件更新所述待执行SQL语句的步骤包括：In one of the embodiments, a corresponding SQL operation condition is generated according to the tombstone field, and the step of updating the SQL statement to be executed according to the SQL operation condition includes:
若所述操作类型为更新或者查询，则生成X.is_delete＝K1的第一操作条件，在所述待执行SQL语句的where属性中增加所述第一操作条件；若所述操作类型为删除，则生成X.is_delete＝K2的第二操作条件，根据所述待执行SQL语句创建一个更新类型的SQL语句，在所述更新类型的SQL语句的set属性中增加所述第二操作条件。If the operation type is update or query, then generate the first operation condition of X.is_delete=K1, increase the first operation condition in the where attribute of the SQL statement to be executed; if the operation type is delete, Then generate the second operation condition of X.is_delete=K2, create an update-type SQL statement according to the SQL statement to be executed, and add the second operation condition to the set attribute of the update-type SQL statement.
其中，X表示待执行SQL语句指向的数据表中的数据项，is_delete表示逻辑删除字段，K1、K2表示预设的逻辑值，当X.is_delete＝K1时，表示数据X未被逻辑删除，当X.is_delete＝K2时，表示数据X被逻辑删除。Among them, X represents the data item in the data table pointed to by the SQL statement to be executed, is_delete represents the tombstone field, and K1 and K2 represent preset logical values. When X.is_delete=K1, it means that the data X has not been tombstoned. When X.is_delete=K2, it means that data X is logically deleted.
下面以查询SQL语句和删除SQL语句为例，对待执行SQL语句的更新实现进行说明:The following takes querying SQL statements and deleting SQL statements as examples to illustrate the update implementation of SQL statements to be executed:
假设待执行的查询SQL语句为：Suppose the query SQL statement to be executed is:
SELECT u.*,th.*ROM t_user_info u LEFT JOIN t_user_health th ON u.id＝th.user_id WHERE u.name＝‘test’；SELECT u.*,th.*ROM t_user_info u LEFT JOIN t_user_health th ON u.id=th.user_id WHERE u.name='test';
解析该SQL语句得到语法树如图3所示，当遍历到SQLExprTableSource节点时,获取到对应的表名,因为实体类和数据表是有映射关系的,因此可以进一步找到对应的实体类；判断实体类中有没有@LogicDelete标志,如果有，则根据@LogicDelete标志所针对的属性映射出数据表中对应的字段名,之后在待执行查询SQL语句的Where节点添加对应的逻辑删除条件,例如加上is_delete＝0作为查询条件。基于此，得到上述待执行的查询SQL语句对应的，更新后的SQL语句为：Parse the SQL statement to get the syntax tree as shown in Figure 3. When traversing to the SQLExprTableSource node, the corresponding table name is obtained. Because the entity class and the data table have a mapping relationship, the corresponding entity class can be further found; determine the entity Is there a @LogicDelete flag in the class? If so, map the corresponding field name in the data table according to the attribute targeted by the @LogicDelete flag, and then add the corresponding logical deletion condition to the Where node of the query SQL statement to be executed, for example, add is_delete=0 is used as a query condition. Based on this, the updated SQL statement corresponding to the above query SQL statement to be executed is:
SELECT u.*,th.*ROM t_user_info u LEFT JOIN t_user_health th ON u.id＝th.user_id WHERE u.name＝‘test’AND u.is_delete＝0AND th.is_delete＝0。SELECT u.*,th.*ROM t_user_info u LEFT JOIN t_user_health th ON u.id=th.user_id WHERE u.name='test' AND u.is_delete=0 AND th.is_delete=0.
可以理解的，若待执行SQL语句为更新SQL语句，更加上述同样的原理修改待执行SQL语句，使得被逻辑删除的数据不能被修改，提高了SQL更新结果的准确度。Understandably, if the SQL statement to be executed is an update SQL statement, the SQL statement to be executed is modified based on the same principle as above, so that logically deleted data cannot be modified, and the accuracy of the SQL update result is improved.
假设待执行的删除SQL语句为:Suppose the delete SQL statement to be executed is:
delete from t_user_info u where u.name＝test’；delete from t_user_info u where u.name=test';
解析该待执行的删除SQL语句，得到语法树如图4所示；对于delete操作,无法根据遍历SQL语法树去直接修改其中的节点内容，以更新该删除SQL语句,因为根节点已经是不同了。对此，可重新构造UpdateStatement的方式生成更新SQL语句对应的分析树,因为如果要改成更新SQL语句,From和Where都是相同的,因此构造UpdateStatement节点时候,可以根据删除SQL语句的DeleteStatement的From和Where进行赋值，而UpdateStatement比DeleteStatement多一个SetItem节点,这个SetItem节点就是更新SQL语句里面is_deleted＝1这个语句的语法树节点。基于此可以将原删除SQL语句的删除语句转换成逻辑删除中的删除语句。生成新的SQL语句后替换原有SQL语句去执行,即可达到逻辑删除的目的。基于此，得到上述待执行的删除SQL语句对应的更新SQL语句为：Analyze the delete SQL statement to be executed, and get the syntax tree as shown in Figure 4; for the delete operation, it is impossible to directly modify the node content according to traversing the SQL syntax tree to update the delete SQL statement, because the root node is already different . In this regard, the update statement can be restructured to generate the analysis tree corresponding to the update SQL statement, because if you want to update the SQL statement, From and Where are the same, so when constructing the UpdateStatement node, you can delete the SQL statement according to the From of the DeleteStatement It is assigned with Where, and UpdateStatement has one more SetItem node than DeleteStatement. This SetItem node is the syntax tree node of the statement is_deleted=1 in the update SQL statement. Based on this, the delete statement of the original delete SQL statement can be converted into a delete statement in logical delete. After generating a new SQL statement to replace the original SQL statement to execute, the purpose of logical deletion can be achieved. Based on this, the update SQL statement corresponding to the delete SQL statement to be executed is obtained as follows:
update t_user_info u set u.is_deleted＝1where u.name＝test’。update t_user_info_set u.is_deleted=1 where u.name=test'.
在一个实施例中，本申请SQL执行方法的实现途径可以为：预先按照mybatis的规范编写一个mybaits插件,该插件的功能是对待执行SQL语句进行修改,达到改造待执行SQL语句的目的，其原理是对Mybatis中StatementHandler中prepare方法进行拦截,也就是在执行prepare方法的时候会先调用插件的方法。Mybatis是一个比较规范而且灵活的框架,提供实体类,通过该mybaits插件本身提供了接口可实现修改待执行SQL语句的目的,因此可很方便的实现本申请的对逻辑删除的SQL执行方法。In one embodiment, the implementation method of the SQL execution method of the present application can be: write a mybaits plug-in in advance according to the specification of mybatis, the function of the plug-in is to modify the SQL statement to be executed, so as to achieve the purpose of transforming the SQL statement to be executed, its principle It is to intercept the prepare method in StatementHandler in Mybatis, that is, the method of the plug-in will be called first when the prepare method is executed. Mybatis is a relatively standardized and flexible framework that provides entity classes. Through the interface provided by the mybaits plug-in itself, the purpose of modifying the SQL statement to be executed can be realized. Therefore, the SQL execution method for tombstone deletion in this application can be easily realized.
可以理解的，也可以通过数据访问层的其他框架实现上述的对逻辑删除的SQL执行方法，例如基于Hibernate或者JDBC实现。Hibernate封装得比较多,本身没有提供接口去改写SQL语句,因此实现起来难度较大。JDBC是自己写SQL去执行,可在执行SQL前通过动态代理(模拟mybatis插件)实现对SQL语句的改写,但JDBC没有做什么封装,所以要实现本申请的SQL执行方法，需要用户另行增加实体类并加上逻辑删除标志,但是动态代理是JDBC本身没提供的,需要用户另行去实现。It can be understood that the above-mentioned SQL execution method for tombstone deletion can also be implemented through other frameworks of the data access layer, such as based on Hibernate or JDBC. Hibernate encapsulates more and does not provide an interface to rewrite SQL statements, so it is more difficult to implement. JDBC writes SQL by itself to execute, and can rewrite SQL statements through dynamic proxy (simulating mybatis plug-in) before executing SQL, but JDBC does not do any encapsulation, so in order to realize the SQL execution method of this application, users need to add additional entities class and add the tombstone flag, but the dynamic proxy is not provided by JDBC itself, and the user needs to implement it separately.
通过上述实施例，能自动对待执行SQL进行转换,配置简单,理论上能适应所有的SQL语句，用户在写SQL的时候无需关注到底是delete数据还是逻辑删除数据.也不需要自己在写逻辑删除的操作SQL。Through the above embodiment, the SQL to be executed can be automatically converted, the configuration is simple, and it can be adapted to all SQL statements in theory. When writing SQL, the user does not need to pay attention to whether it is delete data or tombstone data. There is no need to write tombstone by yourself Operational SQL.
应该理解的是，对于前述的各方法实施例，虽然流程图中的各个步骤按照箭头的指示依次显示，但是这些步骤并不是必然按照箭头指示的顺序依次执行。除非本文中有明确的说明，这些步骤的执行并没有严格的顺序限制，这些步骤可以以其它的顺序执行。而且，方法实施例的流程图中的至少一部分步骤可以包括多个子步骤或者多个阶段，这些子步骤或者阶段并不必然是在同一时刻执行完成，而是可以在不同的时刻执行，这些子步骤或者阶段的执行顺序也不必然是依次进行，而是可以与其它步骤或者其它步骤的子步骤或者阶段的至少一部分轮流或者交替地执行。It should be understood that, for the foregoing method embodiments, although the steps in the flow chart are shown sequentially as indicated by the arrows, these steps are not necessarily executed sequentially in the order indicated by the arrows. Unless otherwise specified herein, there is no strict order restriction on the execution of these steps, and these steps can be executed in other orders. Moreover, at least some of the steps in the flow chart of the method embodiment may include multiple sub-steps or multiple stages, these sub-steps or stages are not necessarily executed at the same time, but may be executed at different time, these sub-steps Or the order of execution of the stages is not necessarily performed sequentially, but may be performed alternately or alternately with at least a part of other steps or substeps of other steps or stages.
基于与上述实施例中的对逻辑删除的SQL执行方法相同的思想，本文还提供对逻辑删除的SQL执行装置。Based on the same idea as the method for executing SQL for tombstone in the above embodiments, this document also provides an apparatus for executing SQL for tombstone.
在一个实施例中，如图6所示，本实施例的对逻辑删除的SQL执行装置包括：In one embodiment, as shown in FIG. 6, the SQL execution device for tombstone in this embodiment includes:
类型识别模块501，用于获取待执行SQL语句，识别所述待执行SQL语句的操作类型；A type identification module 501, configured to obtain the SQL statement to be executed, and identify the operation type of the SQL statement to be executed;
字段识别模块502，用于当所述操作类型为删除、更新或者查询时，根据实体类中预设的逻辑删除标志，识别待执行SQL语句针对的数据表中的逻辑删除字段；所述实体类与所述数据表之间有映射关系；The field identification module 502 is used to identify the tombstone field in the data table targeted by the SQL statement to be executed according to the preset tombstone flag in the entity class when the operation type is delete, update or query; the entity class There is a mapping relationship with the data table;
以及，修改执行模块503，用于根据所述逻辑删除字段生成对应的SQL操作条件，根据所述SQL操作条件更新所述待执行SQL语句，执行更新得到的SQL语句。And, the modification execution module 503 is configured to generate a corresponding SQL operation condition according to the tombstone field, update the SQL statement to be executed according to the SQL operation condition, and execute the updated SQL statement.
在一实施例中，所述字段识别模块502包括：In one embodiment, the field identification module 502 includes:
类识别单元，用于当所述操作类型为删除、更新或者查询时，确定所述待执行SQL语句对应的实体类；以及，字段识别单元，用于检测所述实体类中预设的逻辑删除标志，根据逻辑删除标志识别数据表中的逻辑删除字段。A class identification unit for determining the entity class corresponding to the SQL statement to be executed when the operation type is delete, update or query; and a field identification unit for detecting preset tombstones in the entity class Flag to identify the tombstone field in the data table based on the tombstone flag.
在一实施例中，所述SQL执行装置还包括：第一执行模块，用于若检测到所述实体类中不包含预设的逻辑删除标志，执行所述待执行SQL语句。In an embodiment, the SQL execution device further includes: a first execution module, configured to execute the SQL statement to be executed if it is detected that the entity class does not contain a preset tombstone flag.
在一实施例中，所述SQL执行装置还包括：第二执行模块，用于当确定出所述待执行SQL语句的操作类型为新增操作类型时，执行所述待执行SQL语句。In an embodiment, the SQL execution device further includes: a second execution module, configured to execute the SQL statement to be executed when it is determined that the operation type of the SQL statement to be executed is a newly added operation type.
在一实施例中，所述类识别单元包括：数据表识别子单元和类识别子单元。In an embodiment, the class identification unit includes: a data table identification subunit and a class identification subunit.
其中，数据表识别子单元，用于根据预设的SQL解析器对所述待执行SQL语句进行解析,得到所述待执行SQL语句对应的语法树；遍历所述语法树的FROM节点和/或FROM节点的叶子节点，确定待执行SQL语句针对的数据表；类识别子单元，用于根据实体类与数据表的映射关系，得到所述数据表对应的实体类，确定为所述待执行SQL语句对应的实体类。Wherein, the data table identification subunit is used to analyze the SQL statement to be executed according to a preset SQL parser to obtain the syntax tree corresponding to the SQL statement to be executed; traverse the FROM node and/or of the syntax tree The leaf node of the FROM node determines the data table targeted by the SQL statement to be executed; the class identification subunit is used to obtain the entity class corresponding to the data table according to the mapping relationship between the entity class and the data table, and is determined as the SQL to be executed The entity class corresponding to the statement.
在一实施例中，所述字段识别单元包括：字段确定子单元，用于确定实体类中逻辑删除标志所指向的属性，根据实体类中属性与数据表中字段的映射关系，得到所述属性对应的字段，作为逻辑删除字段。In one embodiment, the field identification unit includes: a field determination subunit, configured to determine the attribute pointed to by the tombstone flag in the entity class, and obtain the attribute according to the mapping relationship between the attribute in the entity class and the field in the data table The corresponding field is used as a tombstone field.
在一实施例中，所述修改执行模块503包括：第一修改单元和第二修改单元。In an embodiment, the modification execution module 503 includes: a first modification unit and a second modification unit.
其中，第一修改单元，用于若所述操作类型为更新或者查询，则生成X.is_delete＝K1的第一操作条件，在所述待执行SQL语句的where属性中增加所述第一操作条件；第二修改单元，用于若所述操作类型为删除，则生成X.is_delete＝K2的第二操作条件，根据所述待执行SQL语句创建一个更新类型的SQL语句，在所述更新类型的SQL语句的set属性中增加所述第二操作条件。Wherein, the first modification unit is configured to generate a first operation condition of X.is_delete=K1 if the operation type is update or query, and add the first operation condition to the where attribute of the SQL statement to be executed The second modification unit is used to generate a second operating condition of X.is_delete=K2 if the operation type is deletion, and create an update type SQL statement according to the SQL statement to be executed, in the update type The second operation condition is added to the set attribute of the SQL statement.
其中，X表示待执行SQL语句指向的数据表中的数据项，is_delete表示逻辑删除字段，K1、K2表示预设的逻辑值，当X.is_delete＝K1时，表示数据X未被逻辑删除，当X.is_delete＝K2时，表示数据X被逻辑删除。Among them, X represents the data item in the data table pointed to by the SQL statement to be executed, is_delete represents the tombstone field, and K1 and K2 represent preset logical values. When X.is_delete=K1, it means that the data X has not been tombstoned. When X.is_delete=K2, it means that data X is logically deleted.
通过上述实施例的SQL执行装置,实现了自动对待执行SQL进行转换,能适应所有的SQL语句。用户在编辑SQL语句的时候，无需关注数据库中的数据是否被逻辑删除，通过常规的SQL语句，便可实现对逻辑删除的数据的SQL操作，既简化了用户的SQL操作难度，又提高的SQL操作结果的准确度。Through the SQL execution device of the above embodiment, automatic conversion of the SQL to be executed is realized, and it can adapt to all SQL statements. When users edit SQL statements, they don’t need to pay attention to whether the data in the database is logically deleted. Through regular SQL statements, SQL operations on logically deleted data can be realized, which not only simplifies the difficulty of SQL operations for users, but also improves SQL The accuracy of the result of the operation.
关于对逻辑删除的SQL执行装置的具体限定可以参见上文中对于对逻辑删除的SQL执行方法的限定，在此不再赘述。上述对逻辑删除的SQL执行装置中的各个模块可全部或部分通过软件、硬件及其组合来实现。上述各模块可以硬件形式内嵌于或独立于计算机设备中的处理器中，也可以以软件形式存储于计算机设备中的存储器中，以便于处理器调用执行以上各个模块对应的操作。For specific limitations on the SQL execution device for tombstones, refer to the above-mentioned limitations on the SQL execution method for tombstones, which will not be repeated here. Each module in the above-mentioned SQL execution device for tombstone can be fully or partially realized by software, hardware and a combination thereof. The above-mentioned modules can be embedded in or independent of the processor in the computer device in the form of hardware, and can also be stored in the memory of the computer device in the form of software, so that the processor can invoke and execute the corresponding operations of the above-mentioned modules.
此外，上述示例的对逻辑删除的SQL执行装置的实施方式中，各程序模块的逻辑划分仅是举例说明，实际应用中可以根据需要，例如出于相应硬件的配置要求或者软件的实现的便利考虑，将上述功能分配由不同的程序模块完成，即将所述对逻辑删除的SQL执行装置的内部结构划分成不同的程序模块，以完成以上描述的全部或者部分功能。In addition, in the implementation of the SQL execution device for logical delete in the above example, the logical division of each program module is only an example. In actual application, it can be considered according to the needs, such as the configuration requirements of the corresponding hardware or the convenience of software implementation. , the above function allocation is completed by different program modules, that is, the internal structure of the SQL execution device for tombstone is divided into different program modules, so as to complete all or part of the functions described above.
在一个实施例中，提供了一种计算机设备，包括存储器和处理器，存储器中存储有计算机程序，该处理器执行计算机程序时实现上述任一实施例的对逻辑删除的SQL执行方法的步骤。In one embodiment, a computer device is provided, including a memory and a processor, wherein a computer program is stored in the memory, and the processor implements the steps of the SQL execution method for tombstone deletion in any of the above embodiments when executing the computer program.
在一个实施例中，提供了一种计算机可读存储介质，其上存储有计算机程序，计算机程序被处理器执行时实现上述任一实施例的对逻辑删除的SQL执行方法的步骤。In one embodiment, a computer-readable storage medium is provided, on which a computer program is stored, and when the computer program is executed by a processor, the steps of the SQL execution method for tombstone deletion in any of the above-mentioned embodiments are implemented.
本领域普通技术人员可以理解实现上述实施例方法中的全部或部分流程，是可以通过计算机程序来指令相关的硬件来完成，所述的计算机程序可存储于一非易失性计算机可读取存储介质中，该计算机程序在执行时，可包括如上述各方法的实施例的流程。其中，本申请所提供的各实施例中所使用的对存储器、存储、数据库或其它介质的任何引用，均可包括非易失性和/或易失性存储器。非易失性存储器可包括只读存储器(ROM)、可编程ROM(PROM)、电可编程ROM(EPROM)、电可擦除可编程ROM(EEPROM)或闪存。易失性存储器可包括随机存取存储器(RAM)或者外部高速缓冲存储器。作为说明而非局限，RAM以多种形式可得，诸如静态RAM(SRAM)、动态RAM(DRAM)、同步DRAM(SDRAM)、双数据率SDRAM(DDRSDRAM)、增强型SDRAM(ESDRAM)、同步链路(Synchlink)DRAM(SLDRAM)、存储器总线(Rambus)直接RAM(RDRAM)、直接存储器总线动态RAM(DRDRAM)、以及存储器总线动态RAM(RDRAM)等。Those of ordinary skill in the art can understand that all or part of the processes in the methods of the above embodiments can be implemented through computer programs to instruct related hardware, and the computer programs can be stored in a non-volatile computer-readable memory In the medium, when the computer program is executed, it may include the processes of the embodiments of the above-mentioned methods. Wherein, any references to memory, storage, database or other media used in the various embodiments provided in the present application may include non-volatile and/or volatile memory. Nonvolatile memory can include read only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM), or flash memory. Volatile memory can include random access memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in many forms such as Static RAM (SRAM), Dynamic RAM (DRAM), Synchronous DRAM (SDRAM), Double Data Rate SDRAM (DDRSDRAM), Enhanced SDRAM (ESDRAM), Synchronous Chain Synchlink DRAM (SLDRAM), memory bus (Rambus) direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM), etc.
以上实施例的各技术特征可以进行任意的组合，为使描述简洁，未对上述实施例中的各个技术特征所有可能的组合都进行描述，然而，只要这些技术特征的组合不存在矛盾，都应当认为是本说明书记载的范围。The technical features of the above embodiments can be combined arbitrarily. To make the description concise, all possible combinations of the technical features in the above embodiments are not described. However, as long as there is no contradiction in the combination of these technical features, they should be It is considered to be within the range described in this specification.
在上述实施例中，对各个实施例的描述都各有侧重，某个实施例中没有详述的部分，可以参见其它实施例的相关描述。In the foregoing embodiments, the descriptions of each embodiment have their own emphases, and for parts not described in detail in a certain embodiment, reference may be made to relevant descriptions of other embodiments.
本文实施例的术语“包括”和“具有”以及它们任何变形，意图在于覆盖不排他的包含。例如包含了一系列步骤或(模块)单元的过程、方法、系统、产品或设备没有限定于已列出的步骤或单元，而是可选地还包括没有列出的步骤或单元，或可选地还包括对于这些过程、方法、产品或设备固有的其它步骤或单元。The terms "comprising" and "having" and any variations thereof in the embodiments herein are intended to cover non-exclusive inclusion. For example, a process, method, system, product or device comprising a series of steps or (module) units is not limited to the listed steps or units, but optionally also includes steps or units not listed, or optionally It also includes other steps or elements inherent to these processes, methods, products or devices.
在本文中提及的“多个”是指两个或两个以上。“和/或”，描述关联对象的关联关系，表示可以存在三种关系，例如，A和/或B，可以表示：单独存在A，同时存在A和B，单独存在B这三种情况。字符“/”一般表示前后关联对象是一种“或”的关系。The "plurality" mentioned herein means two or more. "And/or" describes the association relationship of associated objects, indicating that there may be three types of relationships, for example, A and/or B may indicate: A exists alone, A and B exist simultaneously, and B exists independently. The character "/" generally indicates that the contextual objects are an "or" relationship.
在本文中提及的“第一\第二”仅仅是是区别类似的对象，不代表针对对象的特定排序，可以理解地，“第一\第二”在允许的情况下可以互换特定的顺序或先后次序。应该理解“第一\第二”区分的对象在适当情况下可以互换，以使这里描述的实施例能够以除了在这里图示或描述的那些以外的顺序实施。The "first\second" mentioned in this article is only to distinguish similar objects, and does not represent a specific ordering of objects. Understandably, "first\second" can be interchanged with specific sequence or sequence. It should be understood that the terms "first\second" are interchangeable under appropriate circumstances such that the embodiments described herein can be practiced in sequences other than those illustrated or described herein.
以上所述实施例仅表达了本申请的几种实施方式，其描述较为具体和详细，但并不能因此而理解为对发明专利范围的限制。应当指出的是，对于本领域的普通技术人员来说，在不脱离本申请构思的前提下，还可以做出若干变形和改进，这些都属于本申请的保护范围。因此，本申请专利的保护范围应以所附权利要求为准。The above-mentioned embodiments only represent several implementation modes of the present application, and the description thereof is relatively specific and detailed, but it should not be construed as limiting the scope of the patent for the invention. It should be noted that those skilled in the art can make several modifications and improvements without departing from the concept of the present application, and these all belong to the protection scope of the present application. Therefore, the scope of protection of the patent application should be based on the appended claims.
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201810225650.4ACN108563694B (en) | 2018-03-19 | 2018-03-19 | SQL execution method and device for logic deletion, computer equipment and storage medium |
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201810225650.4ACN108563694B (en) | 2018-03-19 | 2018-03-19 | SQL execution method and device for logic deletion, computer equipment and storage medium |
| Publication Number | Publication Date |
|---|---|
| CN108563694Atrue CN108563694A (en) | 2018-09-21 |
| CN108563694B CN108563694B (en) | 2021-04-13 |
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201810225650.4AActiveCN108563694B (en) | 2018-03-19 | 2018-03-19 | SQL execution method and device for logic deletion, computer equipment and storage medium |
| Country | Link |
|---|---|
| CN (1) | CN108563694B (en) |
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN109271404A (en)* | 2018-10-25 | 2019-01-25 | 北京华宇信息技术有限公司 | Database is anti-error to delete method, apparatus, electronic equipment and computer-readable medium |
| CN110309196A (en)* | 2019-05-22 | 2019-10-08 | 深圳壹账通智能科技有限公司 | Block chain data storage and query method, apparatus, equipment and storage medium |
| CN110515973A (en)* | 2019-08-30 | 2019-11-29 | 上海达梦数据库有限公司 | A kind of optimization method of data query, device, equipment and storage medium |
| CN112395302A (en)* | 2019-08-15 | 2021-02-23 | 阿里巴巴集团控股有限公司 | SQL instruction execution method and device, electronic equipment and computer readable medium |
| CN114491673A (en)* | 2022-02-16 | 2022-05-13 | 中通服创立信息科技有限责任公司 | Method, system and storage medium for preventing data tampering |
| CN116010419A (en)* | 2023-02-07 | 2023-04-25 | 江西数字网联信息安全技术有限公司 | A method and device for creating a unique index and optimizing logical deletion |
| CN116521708A (en)* | 2023-04-28 | 2023-08-01 | 北京火山引擎科技有限公司 | Database-based data slice processing method, device, medium and electronic equipment |
| CN116975032A (en)* | 2023-07-14 | 2023-10-31 | 南京领行科技股份有限公司 | Data alignment method, system, electronic device and storage medium |
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| EP0120185B1 (en)* | 1983-02-28 | 1992-01-02 | International Business Machines Corporation | Computing apparatus and method for translating into a linear query a graphic language query |
| CN103473296A (en)* | 2013-09-04 | 2013-12-25 | 国云科技股份有限公司 | A Recycle Bin Mechanism and System Applicable to Cloud Computing |
| CN103678494A (en)* | 2013-11-15 | 2014-03-26 | 北京奇虎科技有限公司 | Method and device for client side and server side data synchronization |
| US20140279850A1 (en)* | 2013-03-14 | 2014-09-18 | Cavium, Inc. | Batch incremental update |
| CN105989195A (en)* | 2015-03-23 | 2016-10-05 | 国际商业机器公司 | Approach and system for processing data in database |
| CN107092685A (en)* | 2017-04-24 | 2017-08-25 | 广州新盛通科技有限公司 | A kind of method that file system and RDBMS store transaction data are used in combination |
| CN107111628A (en)* | 2014-10-31 | 2017-08-29 | 微软技术许可有限责任公司 | Efficient maintenance of columnstore indexes on memory-optimized tables |
| CN107168700A (en)* | 2017-04-30 | 2017-09-15 | 王栋 | Foundational development framework based on the SQL SELECT full-automatic list WEB background management systems driven |
| CN107193910A (en)* | 2017-05-14 | 2017-09-22 | 四川盛世天成信息技术有限公司 | A kind of database tamper resistant method and system applied to data safety class product |
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| EP0120185B1 (en)* | 1983-02-28 | 1992-01-02 | International Business Machines Corporation | Computing apparatus and method for translating into a linear query a graphic language query |
| US20140279850A1 (en)* | 2013-03-14 | 2014-09-18 | Cavium, Inc. | Batch incremental update |
| CN103473296A (en)* | 2013-09-04 | 2013-12-25 | 国云科技股份有限公司 | A Recycle Bin Mechanism and System Applicable to Cloud Computing |
| CN103678494A (en)* | 2013-11-15 | 2014-03-26 | 北京奇虎科技有限公司 | Method and device for client side and server side data synchronization |
| CN107111628A (en)* | 2014-10-31 | 2017-08-29 | 微软技术许可有限责任公司 | Efficient maintenance of columnstore indexes on memory-optimized tables |
| CN105989195A (en)* | 2015-03-23 | 2016-10-05 | 国际商业机器公司 | Approach and system for processing data in database |
| CN107092685A (en)* | 2017-04-24 | 2017-08-25 | 广州新盛通科技有限公司 | A kind of method that file system and RDBMS store transaction data are used in combination |
| CN107168700A (en)* | 2017-04-30 | 2017-09-15 | 王栋 | Foundational development framework based on the SQL SELECT full-automatic list WEB background management systems driven |
| CN107193910A (en)* | 2017-05-14 | 2017-09-22 | 四川盛世天成信息技术有限公司 | A kind of database tamper resistant method and system applied to data safety class product |
| Title |
|---|
| 郑汉垣: "Delphi中如何恢复与物理删除Dbase数据库的记录", 《龙岩师专学报》* |
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN109271404A (en)* | 2018-10-25 | 2019-01-25 | 北京华宇信息技术有限公司 | Database is anti-error to delete method, apparatus, electronic equipment and computer-readable medium |
| CN110309196A (en)* | 2019-05-22 | 2019-10-08 | 深圳壹账通智能科技有限公司 | Block chain data storage and query method, apparatus, equipment and storage medium |
| CN112395302A (en)* | 2019-08-15 | 2021-02-23 | 阿里巴巴集团控股有限公司 | SQL instruction execution method and device, electronic equipment and computer readable medium |
| CN110515973A (en)* | 2019-08-30 | 2019-11-29 | 上海达梦数据库有限公司 | A kind of optimization method of data query, device, equipment and storage medium |
| CN110515973B (en)* | 2019-08-30 | 2022-02-18 | 上海达梦数据库有限公司 | Data query optimization method, device, equipment and storage medium |
| CN114491673A (en)* | 2022-02-16 | 2022-05-13 | 中通服创立信息科技有限责任公司 | Method, system and storage medium for preventing data tampering |
| CN116010419A (en)* | 2023-02-07 | 2023-04-25 | 江西数字网联信息安全技术有限公司 | A method and device for creating a unique index and optimizing logical deletion |
| CN116521708A (en)* | 2023-04-28 | 2023-08-01 | 北京火山引擎科技有限公司 | Database-based data slice processing method, device, medium and electronic equipment |
| CN116975032A (en)* | 2023-07-14 | 2023-10-31 | 南京领行科技股份有限公司 | Data alignment method, system, electronic device and storage medium |
| CN116975032B (en)* | 2023-07-14 | 2024-04-12 | 南京领行科技股份有限公司 | Data alignment method, system, electronic device and storage medium |
| Publication number | Publication date |
|---|---|
| CN108563694B (en) | 2021-04-13 |
| Publication | Publication Date | Title |
|---|---|---|
| CN108563694A (en) | Method, apparatus, computer device and storage medium for performing SQ L for logical deletion | |
| US10216826B2 (en) | Database query system | |
| TWI412945B (en) | Retrieving and persisting objects from/to relational databases | |
| CN105760418B (en) | Method and system for performing cross-column search on relational database table | |
| CN111274263B (en) | Visual database change sentence generation method, device and storage medium | |
| MX2013014800A (en) | Recommending data enrichments. | |
| CN111553556A (en) | Business data analysis method, device, computer equipment and storage medium | |
| CN110046170A (en) | Sentence based on multifile management executes method, apparatus, equipment and medium | |
| CN113761040A (en) | Database and application program bidirectional mapping method, device, medium and program product | |
| WO2019000698A1 (en) | Method and system for generating sql statement, electronic apparatus and readable storage medium | |
| WO2019071898A1 (en) | Electronic device, database query script generation method and storage medium | |
| CN115357590A (en) | Recording method and device for data change, electronic device and storage medium | |
| CN110727777A (en) | Knowledge graph management method and device, computer equipment and storage medium | |
| CN108763341B (en) | Electronic device, automatic table building method and storage medium | |
| CN114281845A (en) | Index generation method, apparatus, electronic device and readable storage medium | |
| JP4719212B2 (en) | Method and apparatus for constructing representations of objects and entities | |
| KR20060067812A (en) | Complex data access | |
| CN115510130A (en) | Report generating method, device, computer equipment and storage medium | |
| CN112416966B (en) | Impromptu query method, impromptu query device, computer device and storage medium | |
| US10311051B1 (en) | Storing modeling alternatives with unitized data | |
| CN113553458A (en) | Data export method and device in graph database | |
| CN101944123A (en) | Simulated batch configuration document management method oriented to semiconductor manufacturing equipment function | |
| CN116594628A (en) | Data tracing method and device and computer equipment | |
| CN116108055A (en) | Database dangerous statement detection method, system, equipment and storage medium | |
| WO2019085354A1 (en) | Excel system interface-based database linkage method, electronic device, and storage medium |
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |