Ubuntu creates mysql database of utf-8 character set
Preface:
Recently, I have been working on this small project which combines MySQL with the crawler, and found that there are many bug s in the specific operation.
Especially the format problem caused by my ignorance of basic commands and some details!
Especially this Chinese string problem!
From Windows to Ubuntu, there will always be!
In windows, there is a visual software Navicat, which can follow the steps and see the attributes of the database I created, but on Ubuntu, only the command line is hard to imagine.
For the first time, I created a database from the command line, thinking that the default would be utf8, but I didn't expect that the default would be Latin.
Copy the code that can be executed smoothly under Windows to Ubuntu, and the following bug s appear:
(1366, u"Incorrect string value: '\\xE6\\x88\\x91\\xE6\\x98\\xAF...' for column 'title' at row 1")
failed!Why? Does the difference between the two systems lead to coding problems?
Actually not! It's just that there's a problem with my database creation!
Reasons for the problem:
Reference link:
Mysql Insert Chinese Error: Incorrect string value:' xE7 xA8 x8B xE5 xBA x8F...' for column'course'at row 1
The reason for this error is that the encoding format of the database is latin1 and I want to insert the Chinese of utf8 into the database.
He changed the encoding format of the original database, and I deleted it directly, and then rebuilt a reliable spectrum.
Modify the original database step:
1. Enter the database first:
mysql -uroot -pThen enter your database password, which is set when installing this.
sudo apt-get install mysql-serverThis password must be remembered, otherwise it will be particularly embarrassing, even if it is uninstalled and reloaded!
This interface sets the input password: 
Enter in the red wire frame, press enter to confirm, and confirm the password once.
My Ubuntu database password was forgotten, embarrassing one!
~/csdn$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 5.7.23-0ubuntu0.16.04.1 (Ubuntu)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>Here is where you need to enter the command.
2. Access to the database
Enter commands:
use csdn;Among them, csdn is the name of the database I created. If you change it into your own, case insensitive. In other tutorials, you will capitalize the parameters and lower-case your variables, as if this is the case.
mysql> use csdn;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> This is where you enter the CSDN database, which you created yourself, or else it will be in the default root database.
3. Viewing Data Table Coding
Enter the following instructions to see what encoding the database is:
mysql> show create table table_1;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table_1 | CREATE TABLE `table_1` (
`num` int(11) NOT NULL,
`blogs_id` int(11) NOT NULL,
`title` tinytext NOT NULL,
`page_view` int(11) NOT NULL,
PRIMARY KEY (`blogs_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
Notice here that the first table is a parameter and must be written. The second table_1 is the table I created.
This was rebuilt later, so at default, you can see charset=utf8.
It should have been like this: 
This is a reference blog diagram, you can see in username and course1, the code is latin1, this is very deadly ah! uuuuuuuuuuu
I recreated a database and tables myself for validation purposes:
1. Create a database, the default is Latin1 encoding!
mysql> create database demo;
Query OK, 1 row affected (0.00 sec2. Show several databases:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| csdn |
| demo |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)3. Switch to demo database:
mysql> use demo;
Database changed
Look at its properties:
mysql> show create database demo;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| demo | CREATE DATABASE `demo` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)4. Create a table:
mysql> create table demo_table(title text not null)It is shown as follows:
mysql> show create table demo_table;
+------------+--------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+--------------------------------------------------------------------------------------------+
| demo_table | CREATE TABLE `demo_table` (
`title` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+------------+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Then create a table set to utf8:
mysql> create table demo_table_1(title text not null)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;Display effect:
mysql> show create table demo_table_1;
+--------------+--------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------+--------------------------------------------------------------------------------------------+
| demo_table_1 | CREATE TABLE `demo_table_1` (
`title` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------------+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)Unexpectedly, the second method can insert Chinese!
mysql> insert into demo_table_1(title) values ('I am Chinese.');
Query OK, 1 row affected (0.00 sec)
5. Gee, a little unexpected ~The title should be changed to: If the above does not work, you can also directly modify the inappropriate coding:
First look at the properties of the table:
mysql> show create table demo_table;
+------------+--------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+--------------------------------------------------------------------------------------------+
| demo_table | CREATE TABLE `demo_table` (
`title` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+------------+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Input:
mysql> alter table demo_table change title title text character set utf8;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
Note: The first table is a parameter, demo_table is the name of your table, change is a parameter, title and title are both your columns and headers, which are the same! And no single quotation marks!
text is your data format. What is it when it is defined? Look at the table above.
character parameter.
set, not added to the original tutorial!
Also parameters
utf8 without quotation marks!
Last; remember to add!
What a mistake!
Final look at the effect?
mysql> show create table demo_table;
+------------+------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+------------------------------------------------------------------------------------------------------+
| demo_table | CREATE TABLE `demo_table` (
`title` text CHARACTER SET utf8
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+------------+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Well, I just changed the encoding format of title, the whole table is not!
So finally add a table change!
Table is a parameter, demo_table is a table name, and the rest are parameters.
That's enough!
mysql> alter table demo_table default character set utf8;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table demo_table;
+------------+---------------------------------------------------------------------------------+
| Table | Create Table |
+------------+---------------------------------------------------------------------------------+
| demo_table | CREATE TABLE `demo_table` (
`title` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)
6. Finally, remove the garbage:
The drop command is good, database is a parameter, demo is the database you built. OK~
mysql> drop database demo;
Query OK, 2 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| csdn |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)Create a database of utf8 directly!
I almost forgot that I finally used to create the utf8 database directly. The command line should write as follows:
CREATE DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;It doesn't matter what case it is. You just need to modify db_name and replace it with your own uuuuuuuuuuu