MySQL Full text Search Simple application

The requirement is this: there is a template table in the project, there is a large field in the template item, the template item information is stored in a JSON string in the field, the advantage is that there is only one table, not the traditional Template table and template items to maintain the two tables, and query time is more efficient, You should only query a table. You do not need to make a table query. Of course, the disadvantage is that if there is a template item in the large section of the condition query will be inconvenient. At the beginning of the discussion, it was decided that the field could be saved and read without a conditional query for that field.

But later, due to further refinement of the requirements, there is a need:

When a customer chooses a specific template item, it is necessary to query the previous template for the existence of the same template item, and if there is a check that does not pass, it is awkward to ask for a conditional query that consists of template items.

The first idea is where itemcode like '% template item a% ' and itemcode like '% template item b% ' itemcode like '% template item c% ' ItemCode like '% Template Item d% ' ...

I found out later, for example, there is a template A contains: template Item A, template item B

Now I'm going to create a new template B contains: Template item A, do not repeat template A with template a. But you can't tell by a like query.

Fortunately before looking at MySQL has full text search this function, in this small tried a, the concrete realization is as follows:

To create a template table:

create table  ' Enquiry_template_test '   (   ' template_id '  bigint ()  NOT  null auto_increment comment  ' template id ',   ' template_name '  varchar ( COLLATE)  utf8_bin DEFAULT NULL COMMENT  ' template name ',   ' company_id '  bigint (  default null comment  ' buyer company ',   ' Company_Name '  varchar ( collate utf8) _bin default null comment  ' buyer company name ',   ' Item_code '  TEXT COLLATE  utf8_bin comment  ' template item information ',   ' Valid_flag '  tinyint (4)  DEFAULT  ' 0 '  comment   ' is valid 0 valid, 1 fails ',   ' created_time '  DATETIME DEFAULT NULL COMMENT  ' Create Time ',    ' user_id '  bigint  NOT NULL COMMENT  ' creator ',   primary key   (' template_id '),  fulltext  (Item_code)  ENGINE=MYISAM AUTO_INCREMENT=1130  Default charsEt=utf8 collate=utf8_bin comment= ' Template table information ' 

Note: MySQL to use full-text search, search engine for MyISAM, the need to add fulltext, can be added when the table, if there is data import, you can add after import, I feel you understand, index, query fast, Plug and go slow.

Because I have a smaller amount of data, I don't care about that.

To migrate data from the original table:

INSERT into Enquiry_template_test SELECT * from Enquiry_template;

To query:

SELECT *from ' enquiry_template_test ' WHERE MATCH (item_code) against (' +0001 +0010-0002-0003-0004-0005-0006-0007-0008 -0009-0010-0011-0012-0014 +0015 ' in BOOLEAN MODE);

Because all the template items are fixed, the code is from 0001 to 0015 respectively.

Current platform pass query for a template with 0001,0010,0015 three template items

Although SQL is ugly, it does at least meet the requirements.

MySQL Full text Search Simple application