Shulou(Shulou.com)06/01 Report--

Application scenarios:

1: when using mysql database to store data, sometimes, when you want to store multiple data separated by commas, when querying, you pass a keyword, and you need to query in comma-separated fields. How to handle this situation?

For example:

When the crawler crawls, specify the keyword query, which can be multiple separated by commas. The corresponding field in the database is: key_word, the stored data, such as: Kaige Java,kaigejava. So now the keyword I want to query is: Kaige java. How to inquire?

2: in the second case, what we update is to first determine whether the specified field contains the specified string. If not, add the specified field to the original field.

For example:

No http://www. was added when crawling the site This. Judge that if nothing is added, update and add http://www. . How to update this?

Preliminary preparation:

Create the database:

CREATE TABLE config_sentiment (

Config_sentiment_id varchar (64) NOT NULL COMMENT 'public opinion configuration table-primary key id'

Config_sentiment_web_name varchar (60) NOT NULL COMMENT 'crawl website name'

Config_sentiment_web_url varchar (500) NOT NULL COMMENT 'crawl website URL'

Config_sentiment_item_name varchar (64) NOT NULL COMMENT 'Project id'

Config_sentiment_rate varchar (5) NOT NULL DEFAULT'1' COMMENT 'crawl frequency defaults to 1 (1 hour)'

Config_sentiment_keyword varchar (500) NOT NULL COMMENT 'crawl keyword. Multi-use, separate'

Config_sentiment_newtime varchar (20) DEFAULT NULL COMMENT 'latest crawl time'

Config_sentiment_number int (20) DEFAULT NULL COMMENT 'latest number of crawls'

Config_sentiment_adduser varchar (64) NOT NULL COMMENT 'founder'

Config_sentiment_addtime varchar (20) NOT NULL COMMENT 'creation time'

Config_sentiment_updateuser varchar (64) DEFAULT NULL COMMENT 'updater'

Config_sentiment_updatetime varchar (20) DEFAULT NULL COMMENT 'update time'

PRIMARY KEY (config_sentiment_id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Insert data:

Insert into config_sentiment (config_sentiment_id,config_sentiment_web_name,config_sentiment_web_url,config_sentiment_item_name,config_sentiment_rate,config_sentiment_keyword,config_sentiment_newtime,config_sentiment_number,config_sentiment_adduser,config_sentiment_addtime,config_sentiment_updateuser,config_sentiment_updatetime) values (2018030909572100) values (2018030909572100) (2018030909570021), (2018030909570021), (2018030909570021), (2018030909570021), (2018030909570021, 2018030909570021, 2018030909570021, 2018030909570021, 2018030909570021, 2018030909570021, 2018030909570021, 2018030909570021, 2018030909570021, 2018030909570021,)

View the data:

As we can see, there is no http:// added to url. There are multiple products in keyword.

In scenario one, there are two solutions:

1: use the most commonly used, fuzzy query. Sql statement:

After like, we found two pieces of data. Although the fuzziness of fuzzy query is forward and backward. But with such vagueness, there is too much data to query. A lot of them are not what I want.

I just want to [manage money] this keyword data. How to solve the accurate query in so much data? Please take a look:

Solution 2:

Using MySQL string function find_in_set ()

Specific sql statement:

SELECT t.config_sentiment_web_url url,t.config_sentiment_keyword keyword FROM config_sentiment t WHERE FIND_IN_SET ('financing', t.config_sentiment_keyword)

Results after query:

That solves the problem.

Pairing question 2: update statement:

UPDATE config_sentiment SET config_sentiment_web_url = CONCAT ('http://www.',config_sentiment_web_url) WHERE LOCATE (' http://',config_sentiment_web_url)=0;)

Execution result:

Check the http://www. again. Whether it has been added to the database.

It's been added. The problem has been solved.

It should be noted that string concatenation in mysql does not use [+] but [concat function].

Brother Kai java (Wechat id:kaigejava)

In this article, Kaige's personal website: http://kaigejava.com/article/detail/122

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.