MySQL cannot store emoji expression solution analysis

This article exemplifies the solution that MySQL cannot store emoji emoticons. Share it for your reference, as follows:

Today, the crawler crawls Bole online articles. Because there are emoji expressions in the articles, the articles with emoji expressions can't be crawled down

After a search, the problem was finally solved.

Related articles can refer to:

① MySQL can't store Emoji expression problem

② Error reporting of mysql expression in emoji

1. In navicat

If you change the coding of the database before creating a new table, it seems that you can change it yourself when building a table

To view character set encoding:

show variables like '%char%';

Change database encoding:

ALTER DATABASE  Database name  CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Change table encoding:

ALTER TABLE  Table name  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

The following ones seem to be useless. If they can't, they can also be used

set @@character_set_client='utf8mb4';
set @@character_set_connection='utf8mb4';
set @@character_set_results='utf8mb4';
set @@character_set_server='utf8mb4';

2. In the crawler code

class MysqlPipeline(object):
  def __init__(self):
    self.conn = MySQLdb.connect('127.0.0.1', 'root', 'root', 'article_spider', charset='utf8mb4', use_unicode=True)
    self.cursor = self.conn.cursor()
  def process_item(self, item, spider):
    insert_sql = '''
      insert into article(title,url,bookmark_nums,url_object_id,content)
      VALUE (%s,%s,%s,%s,%s)
    '''
    self.cursor.execute(insert_sql, (item['title'], item['url'], item['bookmark_nums'],item['url_object_id'], item['content']))
    self.conn.commit()

More readers interested in MySQL can check out the topics on this site: "MySQL Stored Procedure Skills Encyclopedia", "MySQL Common Function Summary", "MySQL Log Operation Skills Encyclopedia", "MySQL Transaction Operation Skills Summary" and "MySQL Database Lock Related Skills Summary"

I hope this article is helpful to everyone's MySQL database.