MySQL-第N篇杂记

1、数据的导入导出

2、查询结果的重定向

3、ON DUPLICATE KEY UPDATE对于指定的主键或者唯一键，insert时发生冲突则进行update操作。

4、解决MySQL中问乱码问题，分了5.7版本、8.0版本

Microsoft Windows [版本 10.0.17763.805]
(c) 2018 Microsoft Corporation。保留所有权利。

C:Userslfy>mysql -uroot -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 12
Server version: 5.7.27-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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> show variables like '%char%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | utf8                                                    |
| character_set_connection | utf8                                                    |
| character_set_database   | latin1                                                  |
| character_set_filesystem | binary                                                  |
| character_set_results    | utf8                                                    |
| character_set_server     | latin1                                                  |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:Program FilesMySQLMySQL Server 5.7sharecharsets |
+--------------------------+---------------------------------------------------------+
8 rows in set, 1 warning (0.01 sec)

mysql>

...
# CLIENT SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by MySQL client applications.
# Note that only client applications shipped by MySQL are guaranteed
# to read this section. If you want your own MySQL client program to
# honor these values, you need to specify it as an option during the
# MySQL client library initialization.
#
[client]
default-character-set=utf8

# [CLIENT_PIPE]=""
# pipe=

# [CLIENT_SOCKET]="socket"
# socket=

# [CLIENT_PORT]="port"
port=3306

[mysql]
no-beep

# [CLIENT_DEFAULT_CHARACTER_SET]=""
# default-character-set=
default-character-set=utf8

[database]
default-character-set=utf8

[server]
default-character-set=utf8

# SERVER SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this 
# file.
#
# [SERVER_TYPE]="server_type"
# server_type=
[mysqld]
character-set-server=utf8

# The next three options are mutually exclusive to SERVER_PORT below.
# [SERVER_SKIP]=""
# skip-networking=
# [SERVER_PIPE]=""
# enable-named-pipe=
# [SHARED_MEMORY]=""
# shared-memory=
...

==>>>

Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。

C:Userslfy>mysql -uroot -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 10
Server version: 8.0.17 MySQL Community Server - GPL

Copyright (c) 2000, 2019, 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> show variables like '%char%'；
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '；' a
t line 1
mysql> show variables like '%char%';
+--------------------------+----------------------------------------------------
-----+
| Variable_name            | Value
     |
+--------------------------+----------------------------------------------------
-----+
| character_set_client     | utf8
     |
| character_set_connection | utf8
     |
| character_set_database   | utf8mb4
     |
| character_set_filesystem | binary
     |
| character_set_results    | utf8
     |
| character_set_server     | utf8mb4
     |
| character_set_system     | utf8
     |
| character_sets_dir       | C:Program FilesMySQLMySQL Server 8.0sharechars
ets |
+--------------------------+----------------------------------------------------
-----+
8 rows in set, 1 warning (0.13 sec)

mysql>

...
# The following options will be read by MySQL client applications.
# Note that only client applications shipped by MySQL are guaranteed
# to read this section. If you want your own MySQL client program to
# honor these values, you need to specify it as an option during the
# MySQL client library initialization.
#
[client]
default-character-set=utf8

# pipe=

# socket=MYSQL

port=3306

[mysql]
no-beep

# default-character-set=
default-character-set=utf8

# SERVER SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this 
# file.
#
# server_type=3
[mysqld]
character-set-server=utf8

# The next three options are mutually exclusive to SERVER_PORT below.
# skip-networking
# enable-named-pipe
# shared-memory
...