The database design problem of one field with multiple values now involves field. The value is single. for example, if the value is 3, there is one field and the value is multiple. For example, the value of field B may be 1, 2, 3, and 4. now we need to find all records containing the value of field. For example, how to design a database reasonably? Fully considering performance optimization. Database design problems with multiple values in one field
Field A is now available. The value is single. for example, the value is 3.
There is one field with multiple values. For example, field B may be 1, 2, 3, or 4.
Now we need to find all records that contain the value of field. For example:
Records:, 5, and 7 (compliant)
Records, 8, and 9 (non-conforming)
How can I design a database reasonably? Fully considering performance optimization.
Share:
------ Solution --------------------
Yes, or use commas to separate them.
------ Solution --------------------
If the value range cannot exceed 64, you can use the set type directly.
If you do not want to use the set type, the value range is more than 64.
The varchar or text type can be used.
Data is composed of strings separated by commas (,), such as 2, 4, 6, and 3.
Mysql provides the find_in_set function for searching.
------ Solution --------------------
Like, find_in_set, and in
They are all looking for "whether it is in it", and the performance will certainly not be high.
Package the data together, and change the space by time.
To achieve high-speed purposes, of course, you cannot package together, but store and add indexes independently. of course, you must add the host id.
This is the so-called space for time
