MySQL build database, build user and build table matters

1,mysql Building the database statement is relatively simple, in a word:

1 Create Database Tppamltest3

2, create user and authorization:

1 Insert  intoMysql.User(Host,User, Password,ssl_cipher,x509_issuer,x509_subject)Values("localhost", "User name", Password ("Password"),"","","");2FlushPrivileges; 3 Grant  All Privileges  onTppamltest3.*  toUser name@localhostIdentified by 'Password';4FlushPrivileges;

Where the authorization statement to the user assigned all permissions, specific other look at Baidu.

3, build the table script:

1  UseTppamltest3;2 3 SETForeign_key_checks=0;4 5 -- ----------------------------6 --Table structure for Cfg_auto_mend7 -- ----------------------------8 DROP TABLE IF EXISTS' cfg_auto_mend ';9 CREATE TABLE' Cfg_auto_mend ' (Ten' ID 'varchar( +) not NULL, One' Mend_sql 'text, A   PRIMARY KEY(' ID ') -) ENGINE=InnoDBDEFAULTCHARSET=UTF8;

Note that there is a default encoding setting at the end.

---------------------------Complete-----------------------------

=================================

Handling of some unusual cases

=================================

Garbled problem:

First, the shell can view the current MySQL code

 like ' %character% ';  

+--------------------------+-------------------------------------+|Variable_name|Value|+--------------------------+-------------------------------------+|Character_set_client|Utf8||Character_set_connection|Utf8||Character_set_database|Gbk||Character_set_filesystem| binary                              ||Character_set_results|Utf8||Character_set_server|Utf8||Character_set_system|Utf8||Character_sets_dir|E:\anzhuan\MySQL5.5\share\charsets\|+--------------------------+-------------------------------------+8Rowsinch Set(0.00Sec

Can change

Set = Character Set

As long as the encoding is consistent, no garbled characters are present.

================================ a little bit of content for excerpt ========================================

1. System code
>show variables like '%character% ';
Mysql> Show variables like '%collation% ';
Change the system code: Modify the default encoding options in MY.CNF (/ETC/MY.CNF) [mysqld] under Add Default-charcter-set=utf8 MySQL version 5.5 and above replaced by character-set-server= UTF8 restarting MySQL
Command form mysql> SET NAMES ' UTF8 '; All settings will expire when MySQL is restarted

2. Database encoding
View database encoding: mysql> show create Database db_name;
Modify database encoding: Mysql> ALTER db_name # # # #这里修改整个数据库的编码
CHARACTER SET UTF8
DEFAULT CHARACTER SET UTF8
COLLATE Utf8_general_ci
DEFAULT COLLATE utf8_general_ci;
Specify the encoding when constructing the database:
mysql> CREATE DATABASE db_name
CHARACTER SET UTF8
DEFAULT CHARACTER SET UTF8
COLLATE Utf8_general_ci
DEFAULT COLLATE utf8_general_ci;
3. Database tables and field encodings

View database tables and field codes: mysql> show CREATE TABLE table_name;
>alter TABLE table_name DEFAULT CHARACTER SET UTF8;
Modify the field code: mysql> ALTER TABLE ' table_name ' change ' dd "' dd ' VARCHAR ' CHARACTER SET UTF8 COLLATE utf8_bin not NULL The command is to change the field encoding of DD in the MySQL database table_name table to UTF8

4. When inserting Chinese characters under the command line, specify the encoding:mysql> set names UTF8; Sometimes this is a very important sentence!
mysql> INSERT into Test (name) VALUES (' Wang Dongwei ');

In short, regardless of the use of the encoding method, as long as the full unity will be able to achieve the corresponding effect.

-------------------------------------------------------------------separate---------------------------------------------------- -------------------

In the query results can be seen in the MySQL database system client, database connection, database, file system, query results, server, system character set here, the file system character set is fixed, the system, the server's character set is determined at the time of installation, is not related to garbled problem. The problem of garbled characters is related to the character set of client, database connection, database, query result.

* Note: The client is to see the way to access the MySQL database, through the command line access, command-line window is the client, through the connection of JDBC and other access, the program is the client when we write Chinese data to MySQL, the client, the database connection, write to the database, respectively, the encoding conversion. When the query is executed, the results are returned, the database connection is made, and the client encodes the conversion separately. Now it should be clear that garbled characters occur in one or more of the databases, clients, query results, and database connections
A link
And then we'll solve the problem.
When we log in to the database, we use the MySQL--default-character-set= character set-U root-p to connect, when we
Then use show variables like '%char% '; command to view character set settings, you can find the client, database connection,
The character set of the query result has been set to the character set selected at login
If you are already logged in, you can use the set names character set; command to achieve the above effect, equivalent to the following command:
Set character_set_client = Character Set
Set character_set_connection = Character Set
Set character_set_results = Character Set
If you are connecting to a database through JDBC, you can write a URL like this:
url=jdbc:mysql://localhost:3306/abs?useunicode=true&characterencoding= Character Set
JSP pages and other terminals to also set the corresponding character set
The database's character set can modify the MySQL startup configuration to specify a character set, or it can be added when create database
Default character set character set to enforce database
With this setting, the entire data is written to the read out process is unified in the character set, there will be no garbled
Why is writing directly to Chinese from the command line not set or garbled?
It is clear from the command line that the character set settings of the client, database connection, query results are unchanged
The input of Chinese after a series of transcoding and back to the original character set, we look to see, of course, not garbled
This does not mean that Chinese are stored correctly in the database as medium characters.
For example, there is now a UTF8 encoding database, client connections using GBK encoding, connection using the default
Iso8859-1 (that is, latin1 in MySQL), we send the string "Chinese" on the client, the client
A string of GBK formatted binary codes will be sent to the connection layer, and the connection layer will be in iso8859-1 format to
The binary code is sent to the database, and the database stores the encoding in UTF8 format, and we will use this field to UTF8
Format read out, it must be garbled, that is, the Chinese data in writing to the database is stored in garbled form,
When the same client makes a query operation, it does a set of actions that are opposite to the write, and the wrong UTF8 format is binary
The code is converted into the correct GBK code and displayed correctly.