Data storage and query method and device based on Mysql database

Abstract

The invention discloses a data storage and query method and device based on a Mysql database. The method comprises the steps that permission information of a user for an application is received; determining an attribute value corresponding to the authority in the authority information according to the authority information and a predefined corresponding relationship between the authority and the attribute value; converting the attribute value into a binary value; using the attribute value and / or the binary value as a permission value; generating an application permission data table; receiving a query request of a to-be-queried application with a specified permission for a user; determining a value of a specified permission according to a pre-defined corresponding relationship between the permission and the attribute value and specified permission information in the query request; according to the value of the specified permission and the user information in the query request, the to-be-queried application information is determined in the application permission data table, the memory occupied by data storage and query based on the Mysql database can be reduced, the number of database tables and the structural complexity are reduced, and the data query efficiency and performance are improved.

Description

Technical Field

[0001] The present invention relates to the field of computer technologies, and particularly to a method and device for data storage and query based on a Mysql database. Background Art

[0002] This section aims to provide background or context for embodiments of the present invention. The description herein is not admitted to be prior art merely because it is included in this section.

[0003] During the daily use of a project, staff often perform these operations: searching for users with "read" and "write" permissions, searching for engineers proficient in "java" and "C++" languages, and searching for systems where users have "operation and maintenance" or "R & D" permissions. All of these require the use of a defined array field. The array field supports data filtering according to a value set. The usage methods of database array fields are diverse, and the advantages and disadvantages of the usage methods directly determine the efficiency and performance of data filtering. There are two existing technical solutions for implementing data filtering according to a set of values of an array field based on a Mysql database: saving the array as a string and storing multiple rows of data.

[0004] As Figure 1 shown, in the technical solution of saving the array as a string: the way of saving the permissions of users in the application in the database is to splice the permissions in the permission array into a string in the form of being separated by a comma ",". When the backend receives a user request and queries for applications where user A has "operation and maintenance" or "R & D" permissions, it is necessary to first load all the permission data of user A into the memory, then convert the permission field of permission into an array, and finally filter which applications' permissions contain "operation and maintenance" or "permission". The final data filtering result is Application 1 and Application 2. The technical solution of saving the array as a string has the following disadvantages: it occupies a large amount of memory: it is necessary to load all the permissions of the user into the memory, and most of this data is invalid data. When the amount of table data is too large, it is easy to cause problems such as oom (out of memory).

[0005] As Figure 2As shown in the figure, in the technical solution of multi-line data storage: the way of saving the user's permissions in the application in the database is: taking "user-application-permission" as the dimension and storing it in a single row. For example, if user A is both an operation and maintenance staff and a R & D staff in application 1, then the data stored in the database table is two rows: user A-application 1-1 (operation and maintenance), user A-application 1-2 (R & D). When the backend receives a user request and queries the applications created by user A and participated in by user A, it only needs to tell the database the filtering conditions: user A, and the permission permission is 1 or 2, then the database can filter out the data that meets the requirements: application 1 and application 2. The technical solution of multi-line data storage has the following disadvantages: 1. Increase the complexity of the database table structure: Since the relationship between users, applications, and permissions is many-to-many, a relationship table is required to save the "user-application-permission" mapping relationship, and it is impossible to use a single table to implement permission control, increasing the number and complexity of tables. 2. Multiply the amount of table data and reduce performance: For each permission that a user has in an application, the same number of new data records need to be added. When filtering data in the database each time, the amount of data to be scanned expands exponentially, reducing the query performance. When the amount of table data is too large, a large response delay and database CPU pressure will be generated. Summary of the Invention

[0006] An embodiment of the present invention provides a data storage and query method based on a Mysql database, which is used to reduce the memory occupied by data storage and query based on the Mysql database, reduce the number and structural complexity of database tables, and improve data query efficiency and performance. The method includes:

[0007] Receiving the permission information that the user has for the application;

[0008] Determining the attribute value corresponding to the permission in the permission information according to the permission information and the pre-defined correspondence between permissions and attribute values;

[0009] Converting the attribute value corresponding to the permission in the permission information into a binary value;

[0010] Taking the attribute value and / or the binary value as the value of the permission; if there are multiple permissions in the permission information, taking the union of the attribute values and / or the binary values to obtain the value attribute of the permission;

[0011] Generating an application permission data table in the Mysql database according to the user information, application information, and the value of the permission; the application permission data table includes the correspondence between the user information, application information, and the value of the permission;

[0012] Receiving a query request for the application with specified permissions that the user has;

[0013] Determining the value of the specified permission according to the pre-defined correspondence between permissions and attribute values and the specified permission information in the query request;

[0014] Determine the application information to be queried in the application permission data table according to the value of the specified permission and the user information in the query request.

[0015] An embodiment of the present invention further provides a data storage and query device based on a Mysql database, which is used to reduce the memory occupied by data storage and query based on the Mysql database, reduce the number of database tables and the complexity of the structure, and improve the data query efficiency and performance. The device includes:

[0016] A permission information receiving module, configured to receive the permission information that the user has for the application;

[0017] An attribute value determination module, configured to determine the attribute value corresponding to the permission in the permission information according to the permission information and the predefined correspondence between permissions and attribute values;

[0018] A binary value determination module, configured to convert the attribute value corresponding to the permission in the permission information into a binary value;

[0019] A permission value determination module, configured to use the attribute value and / or the binary value as the permission value; if there are multiple permissions in the permission information, take the union of the attribute value and / or the binary value to obtain the permission value attribute value;

[0020] A generation module, configured to generate an application permission data table in the Mysql database according to the user information, the application information, and the permission value; the application permission data table includes the correspondence between the user information, the application information, and the permission value;

[0021] A query request receiving module, configured to receive a query request for the application to be queried for which the user has a specified permission;

[0022] A specified permission value determination module, which determines the value of the specified permission according to the predefined correspondence between permissions and attribute values and the specified permission information in the query request;

[0023] A to-be-query application determination module, configured to determine the to-be-query application information in the application permission data table according to the value of the specified permission and the user information in the query request.

[0024] An embodiment of the present invention further provides a computer device, including a memory, a processor, and a computer program stored on the memory and executable on the processor. When the processor executes the computer program, the above-mentioned data storage and query method based on the Mysql database is implemented.

[0025] An embodiment of the present invention further provides a computer-readable storage medium, which stores a computer program. When the computer program is executed by a processor, the above-mentioned data storage and query method based on the Mysql database is implemented.

[0026] An embodiment of the present invention further provides a computer program product, which includes a computer program. When the computer program is executed by a processor, it implements the above-mentioned data storage and query method based on the Mysql database.

[0027] Compared with the method in the prior art of converting an array to a string for saving or storing multiple lines of data to implement data screening according to a set of array field values, the embodiment of the present invention receives the permission information that the user has for the application; determines the attribute value corresponding to the permission in the permission information according to the permission information and the pre-defined correspondence between permissions and attribute values; converts the attribute value corresponding to the permission in the permission information into a binary value; uses the attribute value and / or the binary value as the value of the permission; if there are multiple permissions in the permission information, takes the union of the attribute value and / or the binary value to obtain the value attribute of the permission; generates an application permission data table in the Mysql database according to the user information, application information, and value of the permission; the application permission data table includes the correspondence between the user information, application information, and value of the permission; receives a query request for the application with specified permissions that the user has; determines the value of the specified permission according to the pre-defined correspondence between permissions and attribute values and the specified permission information in the query request; determines the application information to be queried in the application permission data table according to the value of the specified permission and the user information in the query request, which can reduce the memory occupied by data storage and query based on the Mysql database, reduce the number of database tables and the complexity of the structure, and improve the data query efficiency and performance. BRIEF DESCRIPTION OF THE DRAWINGS

[0028] In order to more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the following will briefly introduce the drawings required for use in the description of the embodiments or the prior art. Obviously, the following drawings are only some embodiments of the present invention. For those of ordinary skill in the art, without creative efforts, other drawings can be obtained according to these drawings. In the drawings:

[0029] Figure 1 FIG. 13 is a schematic diagram of one of the data storage and query methods based on the Mysql database in the prior art;

[0030] Figure 2 FIG. is a schematic diagram of another data storage and query method based on the Mysql database in the prior art;

[0031] Figure 3 FIG. 21 is a schematic flowchart of the data storage and query method based on the Mysql database provided by the embodiment of the present invention;

[0032] Figure 4 FIG. 25 is a schematic diagram of the data structure of the permission permission field provided by the embodiment of the present invention;

[0033] Figure 5 Schematic diagram of the data storage method based on the Mysql database provided by the embodiment of the present invention;

[0034] Figure 6 Schematic diagram of the data query method based on the Mysql database provided by the embodiment of the present invention;

[0035] Figure 7 Schematic diagram of a specific example of the data query method based on the Mysql database provided by the embodiment of the present invention;

[0036] Figure 8 Schematic diagram of the structure of the data storage and query device based on the Mysql database provided by the present invention;

[0037] Figure 9 Schematic diagram of the structure of the computer device provided by the present invention. Detailed implementation manners

[0038] To make the objectives, technical solutions and advantages of the embodiments of the present invention clearer and more understandable, the embodiments of the present invention will be further described in detail below with reference to the accompanying drawings. Herein, the illustrative embodiments of the present invention and their descriptions are used to explain the present invention, but not to limit the present invention.

[0039] First, the professional terms involved in this article are introduced:

[0040] Attribute value: The value defined in Java, and value is a computer programming term and is used as an attribute or keyword in many programming languages.

[0041] Array field: The range of field values is fixed, and the field value can store an array, and the element values in the array meet the requirements of the value range.

[0042] Mysql database: A relational database, and the storage format can intuitively reflect the relationship between entities.

[0043] Bit operation: Directly operate on the binary bits of an integer in memory.

[0044] oom: out of memory, Java heap overflow, that is, memory overflow.

[0045] JVM: Java Virtual Machine.

[0046] &: Bit operator, taking the intersection of two data. 1&0 = 0, 1&1 = 1.

[0047] |: Bit operator, taking the union of two data. 1|0 = 1, 0|0 = 0.

[0048] SQL command: Structured Query Language, a language for querying and modifying databases.

[0049] In daily project use, staff often perform these operations: finding users with "read" and "write" permissions, finding engineers proficient in "Java" and "C++" languages, and finding systems where users have "operation and maintenance" or "R & D" permissions. All of these require the use of a defined array field with a fixed value range for the array field. For example, the value range of the permission field is defined as: visitor, operation and maintenance staff, R & D staff, project manager, reserved 1, reserved 2, reserved 3, reserved 4, system administrator. Numeric fields support data filtering according to the value set, such as supporting the selection of data with both [operation and maintenance staff, R & D staff] permissions, or the selection of data with one of the [operation and maintenance staff, R & D staff] permissions. The usage methods of database array fields are diverse, and the advantages and disadvantages of the usage methods directly determine the efficiency and performance of data filtering.

[0050] How to implement data filtering based on a set of values of a certain array field in a MySQL database? There are two common technical solutions: ① Saving the array as a string; ② Storing multiple rows of data.

[0051] ① Saving the array as a string: Taking the Figure 1 query application as an example: The user selects to query the application list for which they have operation and maintenance or R & D permissions. In the database, the way to save the user's permissions for applications is to concatenate the permissions within the permission array into a string in the form separated by commas ",". When the backend receives the user's request and queries the applications for which user A has operation and maintenance or R & D permissions, it needs to first load all the permission data of user A into memory, then convert the permission field of permission into an array, and finally filter which applications' permissions contain "operation and maintenance" or "permissions". The final data filtering result is Application 1 and Application 2.

[0052] Pain points of the solution of saving the array as a string:

[0053] 1. High memory occupancy: It is necessary to load all the permissions of user A into memory, and most of this data is invalid data. When the table data volume is too large, it is easy to cause problems such as oom (out of memory).

[0054] 2. Does not support flexible paging: To implement paging queries, it is necessary to first load all the data from the database into memory and perform data filtering. Only after data filtering can the total number of matching data be obtained, and then paging can be done. If sorted by time or application number, the paging logic is more complex and the difficulty is higher.

[0055] ②Multi-line data storage: Taking Figure 2 the query application in Figure 2 as an example: The user selects to query the application list for which they have operation and maintenance or R & D permissions. The way the database saves the user's permissions for the application is: storing each row separately with the dimension of "user - application - permission". For example, if user A is both an operation and maintenance personnel and engaged in R & D for application 1, then two pieces of data will be stored in the database table: user A - application 1 - 1 (operation and maintenance), user A - application 1 - 2 (R & D). When the backend receives the user's request and queries the applications created by user A and those the user participates in, it only needs to tell the database the filtering conditions: user A, and the permission permission is 1 or 2, then the database can filter out the data that meets the requirements: application 1 and application 2.

[0056] Pain points of multi-line data storage:

[0057] 1. Increase the complexity of the database table structure: Since the relationship between users, applications, and permissions is many-to-many, a relationship table is required to save the "user - application - permission" mapping relationship, and it is impossible to use a single table to implement permission control, increasing the number and complexity of tables.

[0058] 2. Multiply increase the table data volume and reduce performance: For each permission the user has for an application, the same number of new data records need to be added. This causes the amount of data to be scanned to multiply when performing data filtering in the database each time, reducing the query performance. When the table data volume is too large, it will cause a large response delay and database CPU pressure.

[0059] To solve the problems in the existing technology: 1. Occupy a large amount of memory, reduce the performance of the JVM, and even cause oom exceptions; 2. Increase the complexity of the database table structure and increase the difficulty of system operation and maintenance in the later stage; 3. When performing paged queries, it does not support flexible sorting according to fields such as creation time; 4. Multiply increase the data volume in the table, increase the query pressure, and cause response delay or database overload problems, the embodiment of the present invention provides a data storage and query method based on the Mysql database, Figure 3 which is a schematic flowchart of a data storage and query method based on the Mysql database provided by the embodiment of the present invention. As Figure 3 shown, the method may include:

[0060] Step 301, receiving the permission information of the application that the user has;

[0061] Step 302, determining the attribute value corresponding to the permission in the permission information according to the permission information and the pre-defined correspondence between permissions and attribute values;

[0062] Step 303, converting the attribute value corresponding to the permission in the permission information into a binary value;

[0063] Step 304: Use the attribute value and / or binary value as the value of the permission. If there are multiple permissions in the permission information, take the union of the attribute value and / or binary value to obtain the value attribute of the permission.

[0064] Step 305: Generate an application permission data table in the Mysql database according to the user information, application information, and value of the permission. The application permission data table includes the corresponding relationship between the user information, application information, and value of the permission.

[0065] Step 306: Receive a query request for an application for which the user has a specified permission.

[0066] Step 307: Determine the value of the specified permission according to the predefined corresponding relationship between the permission and the attribute value and the specified permission information in the query request.

[0067] Step 308: Determine the application information to be queried in the application permission data table according to the value of the specified permission and the user information in the query request.

[0068] First, introduce the data structure of the permission permission field in the embodiments of the present invention. As Figure 4 shown, the permission field type is an integer with a length of 32 bits, and the underlying storage mechanism is binary data with a length of 32 bits. The definition of the value range of the array field follows the principle of increasing bit by bit in binary: with the left side as the high bit, the storage order from right to left can be: visitor, operation and maintenance personnel, R & D personnel, project manager, (reserving four bits 16, 32, 64, 128), administrator. That is, before step 101, the corresponding relationship between each permission and the attribute value can be predefined. In Figure 4 the attribute value corresponding to the visitor or visitor user is 1, corresponding to the least significant bit of the binary data; the attribute value corresponding to the operation and maintenance personnel is 2, corresponding to the second least significant bit of the binary data; the attribute value corresponding to the R & D personnel is 4, corresponding to the third least significant bit of the binary data; the attribute value corresponding to the project manager is 8, corresponding to the fourth least significant bit of the binary data; the attribute value corresponding to the administrator is 256, corresponding to the eighth least significant bit of the binary data. If the user has other permissions for the application, they can be defined in other significant bits of the permission field. Figure 4 The corresponding relationship between each significant bit, attribute value, and permission in

[0069] Figure 5Schematic diagram of the data storage method based on the Mysql database provided by the embodiments of the present invention. In one embodiment, steps 301 to 305 are the data storage method based on the Mysql database, that is, receiving the permission information of the application possessed by the user and adding permissions for user A; determining the attribute values corresponding to the permissions in the permission information according to the permission information and the pre-defined correspondence between permissions and attribute values. For example Figure 4 Among them: the attribute value corresponding to the visitor user is 1, the attribute value corresponding to the operation and maintenance personnel is 2, the attribute value corresponding to the R & D personnel is 4, the attribute value corresponding to the project manager is 8, and the attribute value corresponding to the administrator is 256.

[0070] In this embodiment, then the attribute value corresponding to the permission can be converted into a binary value. For example, the binary value corresponding to the visitor user is 0000 0001, the binary value corresponding to the operation and maintenance personnel is 0000 0010, the binary value corresponding to the R & D personnel is 0000 0100, the binary value corresponding to the project manager is 0000 1000, and the binary value corresponding to the administrator is 0001 0000. Use an integer field to store whether the object exists bit by bit from low to high, 0 means no, and 1 means yes. As in the above example: with the left side being the high bit, the storage order from right to left is: visitor (user), operation and maintenance personnel, R & D personnel, project manager, (reserving four bits), administrator. 0000 0001 means that the user only has the permission of visitor (user) and no other permissions.

[0071] In this embodiment, the attribute value and / or the binary value can be used as the value of the permission. If there are multiple permissions in the permission information, the union of the attribute value and / or the binary value can be taken to obtain the value of the permission. For example, user A has the permissions of "visitor user", "operation and maintenance personnel", and "R & D personnel" for application 1. The attribute value of the visitor user is 1, the attribute value of the operation and maintenance personnel is 2, and the attribute value of the R & D personnel is 4. Take the union of the permissions of user A: visitor user | operation and maintenance personnel | R & D personnel = 1 | 2 | 4 = 7, or it can also be: visitor user | operation and maintenance personnel | R & D personnel = 0000 0001 | 0000 0010 | 0000 0100 = 0000 0111; take 7 or 0000 0111 as the value of the permission.

[0072] Generate an application permission data table in the Mysql database according to the user information, application information, and the value of the permission; the application permission data table includes the correspondence between the user information, application information, and the value of the permission.

[0073] The permission information table is as Figure 5 shown in the table, and may include user information (user_id), application information (app_id), and the value of the permission (permission (field)). As Figure 5As shown in the figure, on the application platform (collectively referred to as the platform hereinafter) of the data storage and query method based on the Mysql database provided by the embodiments of the present invention, it can be defined that user A has the permissions of "visitor user", "operation and maintenance personnel", and "R & D personnel" for application 1. After submitting the user information, application information, and the permission information that the user has for the application to the backend, the backend calculates the attribute values corresponding to the permissions. The attribute value of the visitor user is 1, the attribute value of the operation and maintenance personnel is 2, and the attribute value of the R & D personnel is 4. The binary value corresponding to the visitor user is 0000 0001, the binary value corresponding to the operation and maintenance personnel is 0000 0010, and the binary value corresponding to the R & D personnel is 0000 0100. The backend takes the union of the permissions of user A: visitor user | operation and maintenance personnel | R & D personnel = 1 | 2 | 4 = 7, visitor user | operation and maintenance personnel | R & D personnel = 0000 0001 | 0000 0010 | 0000 0100 = 0000 0111.

[0074] In one embodiment, according to the user information, application information, and the value of the permission, an application permission data table can be generated in the Mysql database, including: generating a permission record in the Mysql database according to the corresponding relationship of the user information, application information, and the value of the permission; generating an application permission data table according to the permission record; the permission record includes the user information, application information, and the value of the permission that the user has in the application.

[0075] In this embodiment, at the backend of the platform, the Mysql database is called for persistent storage, and the newly added permission record is: user A - application 1 - permission: 7 -> 0000 0111. Figure 4 It can be seen that the values stored in the low three bits of the permission field are the visitor (user), operation and maintenance personnel, and R & D personnel. The storage at the backend conforms to the permission definition of user A by the frontend. The message of successful saving is fed back to the user. By adding a piece of data in the database, multiple permissions of user A can be saved, multiple mapping relationships can be stored, the data volume in the table can be reduced, the number and structural complexity of the database tables can be reduced, the technical difficulty of data persistent processing can be reduced, and the query performance can be improved.

[0076] Figure 6 As shown in the figure, it is a schematic diagram of the data query method based on the Mysql database provided by the embodiments of the present invention. In one embodiment, steps 306 to 308 are the data query methods based on the Mysql database. Taking the example that the user selects to query the application list with the specified permissions that he has, in step 306, a query request for the application to be queried with the specified permissions that the user has is received. For example, user A uses the multi - select box on the frontend of the platform to select options to view the applications for which he has the "operation and maintenance" or "R & D" permissions, and the platform submits the query request to the backend.

[0077] In step 307, determine the value of the specified permission according to the predefined correspondence between permissions and attribute values and the specified permission information in the query request.

[0078] In this embodiment, determining the value of the specified permission according to the predefined correspondence between permissions and attribute values and the specified permission information in the query request may include: determining the attribute value corresponding to the specified permission in the specified permission information according to the predefined correspondence between permissions and attribute values and the specified permission information in the query request; converting the attribute value of the specified permission into a binary value; using the attribute value and / or binary value of the specified permission as the value of the specified permission; if there are multiple specified permissions, obtaining the value of the specified permission after taking the union of the attribute values and / or binary values.

[0079] For example, according to the predefined correspondence between permissions and attribute values, it is determined that the attribute value of the operation and maintenance personnel is 2, and the attribute value of the R & D personnel is 4. The binary value corresponding to the operation and maintenance personnel is 0000 0010, and the binary value corresponding to the R & D personnel is 00000100. Since there are multiple specified permissions, the backend takes the union of the permissions of user A: operation and maintenance personnel | R & D personnel = 2 | 4 = 6; operation and maintenance personnel | R & D personnel = 0000 0010 | 0000 0100 = 0000 0110. Take 6 or 0000 0110 as the value of the specified permission.

[0080] In step 308, determine the application information to be queried in the application permission data table according to the value of the specified permission and the user information in the query request.

[0081] In this embodiment, determining the application information to be queried in the application permission data table according to the value of the specified permission and the user information in the query request may include: determining the permission record related to the user information in the query request in the application permission data table according to the user information in the query request; determining the application information to be queried according to the value of the permission recorded in the permission record and the value of the specified permission.

[0082] For example, the platform backend connects to the Mysql database and executes the SQL command: select * from the application permission data table where user_id = 'user A' and permission & 6. The meaning of this SQL command is: select the permission record with user_id as user A from the application permission data table, and perform an intersection operation on permission in the permission record and 6.

[0083] When submitting a data set for data filtering or querying, bitwise operations are used for filtering data in array fields. For example, in the above example, to view the filtering for permissions of "operation and maintenance" or "R & D", the filtering condition is permission & (operation and maintenance | R & D) => permission & 6; to query the filtering for permissions of both "operation and maintenance" and "R & D", the filtering condition is permission & (operation and maintenance | R & D) = (operation and maintenance | R & D) => permission & 6 = 6. Filtering data in the way of bitwise operations does not require the back-end service to load the data into memory for secondary filtering, occupying less memory and having high data filtering performance.

[0084] In this embodiment, determining the application to be queried according to the permission value recorded in the permission record and the specified permission value may include: if there are multiple specified permissions in the specified permission information, determining whether the multiple specified permissions need to be satisfied simultaneously according to the specified permission information; if not, determining the minimum value of the attribute values corresponding to the specified permissions in the specified permission information; taking the intersection of the permission value recorded in the permission record and the specified permission value; comparing the value after taking the intersection with the minimum value of the attribute values corresponding to the multiple specified permissions, and if the value after taking the intersection is not less than the minimum value of the attribute values corresponding to the multiple specified permissions, determining the application corresponding to the application information included in the permission record as the application information to be queried.

[0085] For example, when user A views the applications for which he has permissions of "operation and maintenance" or "R & D", "or" means that it does not need to be satisfied simultaneously. It is determined that the attribute value of operation and maintenance personnel is 2, and the attribute value of R & D personnel is 4. The minimum value of the attribute values corresponding to the specified permissions in the specified permission information is determined to be 2.

[0086] In Figure 6 the table of

[0087] ① The permission value of user A in application 1 is 7. Take the intersection of the permission value recorded in the permission record and the specified permission value, that is, 7 & 6 = 0000 0111 & 0000 0110 = 0000 0110 = 6. The intersection value of the permission field is 6, and 6 > 2, so application 1 meets the condition, and application 1 is determined as the application to be queried.

[0088] ② The permission value of user A in application 2 is 2. Take the intersection of the permission value recorded in the permission record and the specified permission value, that is, 2 & 6 = 0000 0010 & 0000 0110 = 0000 0010 = 2. The intersection value of the permission field is 2 = 2. The value after taking the intersection is not less than the minimum value of the attribute values corresponding to the multiple specified permissions, so application 2 meets the condition.

[0089] Query result: The back-end finds 2 applications that meet the requirements in the database: application 1 and application 2.

[0090] Figure 7 This is a schematic diagram of a specific example of the data query method based on the Mysql database provided by the embodiment of the present invention. In this embodiment, if there are multiple specified permissions in the specified permission information and multiple specified permissions need to be satisfied simultaneously, the union of the attribute values corresponding to the specified permissions in the specified permission information is taken; the intersection of the value of the permission in the permission record and the value of the specified permission is taken; the value obtained after taking the union of the attribute values corresponding to multiple specified permissions is compared with the value obtained after taking the intersection of the value of the permission in the permission record and the value of the specified permission. If the value obtained after taking the intersection of the value of the permission in the permission record and the value of the specified permission is not less than the value obtained after taking the union of the attribute values corresponding to multiple specified permissions, it is determined that the application corresponding to the application information included in the permission record is the application information to be queried.

[0091] Taking the above case as an example: As Figure 7 shown, when user A views the applications that he / she has both the "operation and maintenance" and "R & D" permissions, the request is submitted to the backend. The attribute value of the operation and maintenance personnel is 2, and the attribute value of the R & D personnel is 4. The backend takes the union of the values of user A's permissions: operation and maintenance personnel | R & D personnel = 2 | 4 = 6. The backend connects to the database and executes the SQL command: select * from user application permission table where user_id = 'user A' and permission & 6 = 6. The meaning of this SQL command is: Select the permission records from the application permission data table where the user_id is user A and the value of the permission field after performing the intersection operation with 6 is 6.

[0092] As Figure 7 shown in the table below, there are two permission records for user A:

[0093] ① The value of user A's permission in application 1 is 7. Take the intersection of the value of the permission in this permission record and the value of the specified permission, that is, 7 & 6 = 0000 0111 & 0000 0110 = 0000 0110 = 6. The intersection value of the permission field is 6 = 6, so application 1 meets the conditions.

[0094] ② The value of user A's permission in application 2 is 2. Take the intersection of the value of the permission in this permission record and the value of the specified permission, that is, 2 & 6 = 0000 0010 & 0000 0110 = 0000 0010 = 2. The intersection value of the permission field is 2 < 6, so application 2 does not meet the conditions.

[0095] Query result: The backend finds 1 application that meets the requirements in the database: application 1.

[0096] In one embodiment, the application permission data table supports sorting according to one or more fields and is displayed on the front end of the platform for users to view.

[0097] An embodiment of the present invention also provides a data storage and query device based on a Mysql database, as described in the following embodiments. The device is as follows Figure 8 shown, the device includes:

[0098] A permission information receiving module 801, configured to receive permission information that a user has for an application;

[0099] An attribute value determining module 802, configured to determine an attribute value corresponding to the permission in the permission information according to the permission information and a pre-defined correspondence between permissions and attribute values;

[0100] A binary value determining module 803, configured to convert the attribute value corresponding to the permission in the permission information into a binary value;

[0101] A permission value determining module 804, configured to use the attribute value and / or the binary value as the permission value; if there are multiple permissions in the permission information, take the union of the attribute value and / or the binary value to obtain the permission value attribute value;

[0102] A generation module 805, configured to generate an application permission data table in the Mysql database according to user information, application information, and the permission value; the application permission data table includes the correspondence between user information, application information, and the permission value;

[0103] A query request receiving module 806, configured to receive a query request for a to-be-query application for which a user has a specified permission;

[0104] A specified permission value determining module 807, configured to determine the value of the specified permission according to a pre-defined correspondence between permissions and attribute values and the specified permission information in the query request;

[0105] A to-be-query application determining module 808, configured to determine to-be-query application information in the application permission data table according to the value of the specified permission and the user information in the query request.

[0106] In one embodiment, the specified permission value determining module 807 is specifically configured to:

[0107] Determine an attribute value corresponding to the specified permission in the specified permission information according to a pre-defined correspondence between permissions and attribute values and the specified permission information in the query request; convert the attribute value of the specified permission into a binary value; use the attribute value and / or the binary value of the specified permission as the value of the specified permission;

[0108] If there are multiple specified permissions, take the union of the attribute value and / or the binary value to obtain the value of the specified permission.

[0109] Generating an application permission data table in the Mysql database according to user information, application information, and the permission value includes:

[0110] Generate permission records in the Mysql database according to user information, application information, and the values of permissions;

[0111] Generate an application permission data table according to the permission records; the permission records include user information, application information, and the values of the permissions that the user has in the application.

[0112] In one embodiment, the application to be queried determination module 808 is specifically configured to:

[0113] According to the user information in the query request, determine the permission records related to the user information in the query request in the application permission data table;

[0114] According to the values of the permissions recorded in the permission record and the values of the specified permissions, determine the application information to be queried.

[0115] In one embodiment, the application to be queried determination module 808 is specifically configured to:

[0116] If there are multiple specified permissions in the specified permission information, determine whether the multiple specified permissions need to be satisfied simultaneously according to the specified permission information;

[0117] If not, determine the minimum value among the attribute values corresponding to the specified permissions in the specified permission information; take the intersection of the values of the permissions recorded in the permission record and the values of the specified permissions; compare the value after taking the intersection with the minimum value among the attribute values corresponding to the multiple specified permissions. If the value after taking the intersection is not less than the minimum value among the attribute values corresponding to the multiple specified permissions, determine the application corresponding to the application information included in the permission record as the application information to be queried;

[0118] If so, take the union of the attribute values corresponding to the specified permissions in the specified permission information; take the intersection of the values of the permissions in the permission record and the values of the specified permissions; compare the value after taking the union of the attribute values corresponding to the multiple specified permissions with the value after taking the intersection of the values of the permissions in the permission record and the values of the specified permissions. If the value after taking the intersection of the values of the permissions in the permission record and the values of the specified permissions is not less than the value after taking the union of the attribute values corresponding to the multiple specified permissions, determine the application corresponding to the application information included in the permission record as the application information to be queried.

[0119] Since the principle of this device for solving problems is similar to the data storage and query method based on the Mysql database, the implementation of this device can refer to the implementation of the data storage and query method based on the Mysql database, and the repeated parts will not be elaborated.

[0120] Based on the foregoing inventive concept, as Figure 9As shown in the figure, the present invention also provides a computer device 900, including a memory 910, a processor 920, and a computer program 930 stored on the memory 910 and executable on the processor 920. When the processor 920 executes the computer program 930, the above-mentioned data storage and query method based on the Mysql database is implemented.

[0121] An embodiment of the present invention also provides a computer-readable storage medium. The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the above-mentioned data storage and query method based on the Mysql database is implemented.

[0122] An embodiment of the present invention also provides a computer program product. The computer program product includes a computer program, and when the computer program is executed by a processor, the above-mentioned data storage and query method based on the Mysql database is implemented.

[0123] Compared with the prior art method of screening data according to a set of array field values by converting an array into a string for storage or storing multiple lines of data, the embodiment of the present invention receives the permission information of the user for the application; determines the attribute value corresponding to the permission in the permission information according to the permission information and the pre-defined correspondence between permissions and attribute values; converts the attribute value corresponding to the permission in the permission information into a binary value; uses the attribute value and / or the binary value as the value of the permission; if there are multiple permissions in the permission information, takes the union of the attribute values and / or the binary values to obtain the value attribute of the permission; generates an application permission data table in the Mysql database according to the user information, application information, and value of the permission; the application permission data table includes the correspondence between the user information, application information, and value of the permission; receives a query request for a to-be-query application with specified permissions of the user; determines the value of the specified permission according to the pre-defined correspondence between permissions and attribute values and the specified permission information in the query request; determines the to-be-query application information in the application permission data table according to the value of the specified permission and the user information in the query request, which can reduce the memory occupied by data storage and query based on the Mysql database, reduce the number of database tables and the complexity of the structure, and improve the data query efficiency and performance.

[0124] Technical effects of the embodiments of the present invention: The Mysql database supports filtering data in a bitwise operation manner, without the need for the backend service to load the data into memory for secondary filtering, occupying less memory and having high data filtering performance. One piece of data in the table can store multiple mapping relationships, eliminating the need for redundant relationship tables, which not only reduces the number and structural complexity of database tables, but also reduces the data volume in the tables, simplifies the technical difficulty of data persistence processing, and improves query performance. The Mysql database filters data in a bitwise operation manner, supports the use of indexes, and improves query efficiency and performance. It supports sorting according to one or more fields, which can well meet the diversity of UI (User Interface) paging and sorting requirements.

[0125] Those skilled in the art should understand that the embodiments of the present invention may be provided as a method, a system, or a computer program product. Therefore, the present invention may take the form of an entirely hardware embodiment, an entirely software embodiment, or an embodiment combining software and hardware aspects. Moreover, the present invention may take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0126] The present invention is described with reference to the flowcharts and / or block diagrams of methods, apparatuses (systems), and computer program products according to the embodiments of the present invention. It should be understood that each flow and / or block in the flowchart and / or block diagram, as well as the combination of flows and / or blocks in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to the processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing devices to generate a machine, such that the instructions executed by the processor of the computer or other programmable data processing devices generate a device for implementing the functions specified in Figure 1 one or more of the flows Figure 1 or blocks or combinations of blocks.

[0127] These computer program instructions can also be stored in a computer-readable memory that can direct a computer or other programmable data processing device to work in a specific manner, such that the instructions stored in the computer-readable memory generate a manufactured article including an instruction device that implements the functions specified in Figure 1 one or more of the flows Figure 1 or blocks or combinations of blocks.

[0128] These computer program instructions can also be loaded onto a computer or other programmable data processing apparatus, so that a series of operation steps are executed on the computer or other programmable apparatus to produce a computer-implemented process, thereby providing instructions for implementing the steps of the process Figure 1 in one process or more processes and / or blocks Figure 1 in one block or more blocks for the functions specified in the claims.

[0129] The specific embodiments described above have further elaborated on the objectives, technical solutions, and beneficial effects of the present invention. It should be understood that the above description is only for the specific embodiments of the present invention and is not intended to limit the protection scope of the present invention. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principles of the present invention should be included within the protection scope of the present invention.

Claims

1. A data storage and query method based on the Mysql database, characterized in that Including: Receiving the permission information possessed by the user for the application; Determining the attribute value corresponding to the permission in the permission information according to the permission information and the predefined correspondence between permissions and attribute values; Converting the attribute value corresponding to the permission in the permission information into a binary value; Taking the attribute value and / or the binary value as the value of the permission; If there are multiple permissions in the permission information, taking the union of the attribute values and / or the binary values to obtain the value attribute of the permission; Generating an application permission data table in the Mysql database according to the user information, the application information, and the value of the permission; the application permission data table includes the correspondence between the user information, the application information, and the value of the permission; Receiving a query request for a to-be-query application for which the user has a specified permission; Determining the value of the specified permission according to the predefined correspondence between permissions and attribute values and the specified permission information in the query request; Determining the to-be-query application information in the application permission data table according to the value of the specified permission and the user information in the query request.

2. The method according to claim 1, characterized in that, Determining the value of the specified permission according to the predefined correspondence between permissions and attribute values and the specified permission information in the query request, including: Determining the attribute value corresponding to the specified permission in the specified permission information according to the predefined correspondence between permissions and attribute values and the specified permission information in the query request; converting the attribute value of the specified permission into a binary value; taking the attribute value and / or the binary value of the specified permission as the value of the specified permission; If there are multiple specified permissions, taking the union of the attribute values and / or the binary values to obtain the value of the specified permission.

3. The method according to claim 1, characterized in that, Generating an application permission data table in the Mysql database according to the user information, the application information, and the value of the permission, including: Generating a permission record in the Mysql database according to the user information, the application information, and the value of the permission; the permission record includes the user information, the application information, and the value of the permission that the user has in the application; Generating an application permission data table according to the permission record.

4. The method according to claim 3, wherein Determining the to-be-query application information in the application permission data table according to the value of the specified permission and the user information in the query request, including: Determining the permission record related to the user information in the query request in the application permission data table according to the user information in the query request; Determining the to-be-query application information according to the value of the permission recorded in the permission record and the value of the specified permission.

5. The method according to claim 4, wherein Determining the to-be-query application information in the application permission data table according to the value of the specified permission and the user information in the query request, including: If there are multiple specified permissions in the specified permission information, determining whether the multiple specified permissions need to be satisfied simultaneously according to the specified permission information; If not, determining the minimum value among the attribute values corresponding to the specified permissions in the specified permission information; taking the intersection of the value of the permission recorded in the permission record and the value of the specified permission; comparing the value after taking the intersection with the minimum value among the attribute values corresponding to the multiple specified permissions, and if the value after taking the intersection is not less than the minimum value among the attribute values corresponding to the multiple specified permissions, determining that the application corresponding to the application information included in the permission record is the to-be-query application; If so, take the union of the attribute values corresponding to the specified permissions in the specified permission information; take the intersection of the value of the permission in this permission record and the value of the specified permission; compare the value obtained after taking the union of the attribute values corresponding to multiple specified permissions with the value obtained after taking the intersection of the value of the permission in this permission record and the value of the specified permission. If the value obtained after taking the intersection of the value of the permission in this permission record and the value of the specified permission is not less than the value obtained after taking the union of the attribute values corresponding to multiple specified permissions, determine that the application corresponding to the application information included in this permission record is the application information to be queried.

6. A data storage and query device based on a Mysql database, characterized in that, Including: A permission information receiving module, configured to receive the permission information that the application has from the user; An attribute value determining module, configured to determine the attribute value corresponding to the permission in the permission information according to the permission information and the predefined correspondence between permissions and attribute values; A binary value determining module, configured to convert the attribute value corresponding to the permission in the permission information into a binary value; A permission value determining module, configured to use the attribute value and / or the binary value as the value of the permission; If there are multiple permissions in the permission information, take the union of the attribute values and / or the binary values to obtain the value attribute of the permission; A generating module, configured to generate an application permission data table in the Mysql database according to the user information, the application information, and the value of the permission; the application permission data table includes the correspondence between the user information, the application information, and the value of the permission; A query request receiving module, configured to receive a query request for the application to be queried with the specified permission by the user; A specified permission value determining module, configured to determine the value of the specified permission according to the predefined correspondence between permissions and attribute values and the specified permission information in the query request; An application to be queried determining module, configured to determine the application information to be queried in the application permission data table according to the value of the specified permission and the user information in the query request.

7. The device according to claim 6, characterized in that, The specified permission value determining module is specifically configured to: Determine the attribute value corresponding to the specified permission in the specified permission information according to the predefined correspondence between permissions and attribute values and the specified permission information in the query request; convert the attribute value of the specified permission into a binary value; use the attribute value and / or the binary value of the specified permission as the value of the specified permission; If there are multiple specified permissions, take the union of the attribute values and / or the binary values to obtain the value of the specified permission.

8. A computer device, comprising a memory, a processor, and a computer program stored on the memory and executable on the processor, characterized in that, When the processor executes the computer program, the method according to any one of claims 1 to 5 is implemented.

9. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program, and when the computer program is executed by the processor, the method according to any one of claims 1 to 5 is implemented.

10. A computer program product, characterized in that, The computer program product includes a computer program, and when the computer program is executed by the processor, the method according to any one of claims 1 to 5 is implemented.

Images