Is it good for mysql to store comma separated storage?

Mysql storage comma separation is not good, the considerations are: 1. Data redundancy and standardization. Every time one of the values ​​needs to be queried or updated, string operations are required to parse and process the comma separation. The value of subvalues, which would involve modifying and re-storing the entire comma-separated string.

Operating system for this tutorial: Windows 10 system, MySQL 8 version, Dell G3 computer.

Using comma-delimited methods for storing data may work in some cases, but it is generally not a good practice.

Here are some considerations:

1. Data redundancy and canonicity:

   If you leave commas If the split values ​​are stored directly in a single field, there will be a problem of data redundancy. Every time you need to query or update one of the values, string operations are required to parse and process the comma-separated values. Such storage violates the normative principle of the database, that is, each attribute should have its own field.

2. Data query and indexing:

   If you need to query based on comma-separated values, string operations and fuzzy matching will be involved. , which will lead to low query efficiency. At the same time, such queries cannot be optimized through ordinary indexes, because indexes generally target a single field.

3. Data update and maintenance:

   If you need to update a sub-value in the comma-separated value, it will involve modifying and re- Stores the entire comma-separated string. This creates additional overhead and is prone to data inconsistencies.

Instead, it is better to split the related values ​​into separate tables and use appropriate relationships to establish the join. For example, if there are multiple values ​​associated with a specific entity, you can use an association table (join table) to store these associations. This will provide better data structure and query performance, and allow for easier data manipulation and maintenance.

In short, try to avoid using comma separation to store data, but use standardized database design and relational models to improve the reliability, queryability and maintainability of data.

The above is the detailed content of Is it good for mysql to store comma separated storage?. For more information, please follow other related articles on the PHP Chinese website!