Shulou(Shulou.com)05/31 Report--

This article is about how to solve the coding problem of MySQL character set. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

In the project, the interrupt command was used to import the sql script and found that it reported coding errors. Later, after some analysis and query, it was found that the character set utf8mb4 was not supported until after mysql 5.5.3, immediately speechless, and then changed the character set coding to utf8.

Note: query MySQL version command:

Mysql-V

MySQL command:

1: use the show statement to find out what databases currently exist on the server:

Mysql > SHOW DATABASES

2:2, create a database MYSQLDATA

Mysql > CREATE DATABASE MYSQLDATA

3: select the database you created

Mysql > USE MYSQLDATA; (if Database changed appears by pressing enter key, the operation is successful!)

4: see what tables exist in the current database

Mysql > SHOW TABLES

5: create a database table

Mysql > CREATE TABLE MYTABLE (name VARCHAR (20), sex CHAR (1))

6: display the structure of the table:

Mysql > DESCRIBE MYTABLE

7: add records to the table

Mysql > insert into MYTABLE values ("hyq", "M")

8: load data into database tables in text (for example, D:/mysql.txt)

Mysql > LOAD DATA LOCAL INFILE "D:/mysql.txt" INTO TABLE MYTABLE

9: import .sql file command (for example, D:/mysql.sql)

Mysql > use database

Mysql > source d:/mysql.sql

10: delete the table

Mysql > drop TABLE MYTABLE

11: clear the table

Mysql > delete from MYTABLE

12: update data in the table

Mysql > update MYTABLE set sex= "f" where name='hyq'

The following is the management experience of using MySql seen inadvertently on the network.

MySql exists as a service in windows. Before using it, make sure that the service has been started and that the net start mysql command is not available to start it. The "/ etc/rc.d/init.d/mysqld start" command can be used when starting in Linux. Note that the initiator should have administrator privileges.

The newly installed MySql contains a root account with an empty password and an anonymous account, which is a big security risk. For some important applications, we should improve the security as much as possible. Here, delete the anonymous account and set the password for the root account. Use the following command:

Use mysql

Delete from User where User= ""

Update User set Password=PASSWORD ('newpassword') where User='root'

If you want to restrict the login terminal used by the user, you can update the Host field of the corresponding user in the User table, and restart the database service after making the above changes. When logging in, you can use the following command:

Mysql-uroot-p

Mysql-uroot-pnewpassword

Mysql mydb-uroot-p

Mysql mydb-uroot-pnewpassword

The above command parameters are part of the commonly used parameters. Please refer to the documentation for details. Mydb here is the name of the database to be logged in.

In the development and practical application, users should not only use root users to connect to the database, although the use of root users for testing is very convenient, but it will bring major security risks to the system, and is not conducive to the improvement of management skills. We give the most appropriate database permissions to the users used in an application. For example, a user who only inserts data should not be given the right to delete data. The user management of MySql is realized through the User table. There are two common ways to add new users: one is to insert the corresponding data row in the User table and set the corresponding permissions at the same time; the other is to create users with certain permissions through the GRANT command. The common uses of GRANT are as follows:

Grant all on mydb.* to NewUserName@HostName identified by "password"

Grant usage on *. * to NewUserName@HostName identified by "password"

Grant select,insert,update on mydb.* to NewUserName@HostName identified by "password"

Grant update,delete on mydb.TestTable to NewUserName@HostName identified by "password"

To give this user the administrative ability to give him permissions on the corresponding object, add the WITH GRANT OPTION option after GRANT. For users who are added by inserting the User table, the Password field is updated and encrypted using the PASSWORD function to prevent unscrupulous people from reading the password. Users who are no longer in use should be cleared, and those whose permissions are out of bounds should be reclaimed in time, which can be done by updating the corresponding fields in the User table or by using REVOKE operation.

The following is an explanation of the common permissions I have obtained from other materials (www.cn-java.com):

Global administrative permissions:

FILE: read and write files on the MySQL server.

PROCESS: displays or kills service threads that belong to other users.

RELOAD: reload access control tables, refresh logs, etc.

SHUTDOWN: turn off the MySQL service.

Database / data Table / data column permissions:

ALTER: modify existing data tables (such as adding / deleting columns) and indexes.

CREATE: create a new database or data table.

DELETE: deletes the record of the table.

DROP: delete a data table or database.

INDEX: create or delete an index.

INSERT: add the record of the table.

SELECT: show / search the records of the table.

UPDATE: modifies records that already exist in the table.

Special permissions:

ALL: allow to do anything (like root).

USAGE: only login is allowed-nothing else is allowed.

Thank you for reading! This is the end of this article on "how to solve the problem of MySQL character set coding". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

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.