Search for individual items in a column with DATABASE regular expression

When adding a column to a table in DATABASE For tables with large amounts of data (tens of millions or more), It takes a lot of time, and if it is serviced in real time, LOCK may occur and service failure may occur.

There are several ways to solve this.

1. There is a way to save the column format in JSON format, which should be checked to see if there is a supported DATABASE.(MYSQL is supported from version 8 or higher.)
2. Create multi-purpose columns in advance and save them in order with separators (,) whenever column items are added.

• If you save it as the content of number 2, you need to search using regular expressions.

Test requirements

DATABASE: MYSQL5.7 version Data Separator: ”|” Data sample:apple|test54|blog

Looking at the regular expression below

^[^\|]{0,}FINDTEXT[^\|]{0,}\|

Put all characters except ^\|{0,} separators before and after the FINDTEXT phrase to make LIKE search possible. If you want to search for EQUL, remove ^\|{0,} at the front and back to become an EQUL condition.

If you want to search for the letter of the second item, you can create a regular expression as shown below.

^[^\|]{0,}\\|[^\|]{0,}FINDTEXT[^\|]{0,}\|

If you solve it, the first part of the sentence comes with all characters other than the delimiter ”|”, and after the first delimiter appears ^\|{0,} delimiters were put before and after the FINDTEXT phrase to enable LIKE search. In this way, if the third item comes, you can add a regular expression in this format.