MySQL安装

推荐使用通用二进制包安装,下载通用二进制包,解压后会有INSTALL-BINARY文件通过查看文件中的安装示例步骤,完成数据库的初级安装    注意:在数据块初始化时最好提前准备好my.cnf文件并指定

groupadd mysql                            useradd -r -g mysql -s /bin/false mysqlcd /usr/localtar zxvf /path/to/mysql-VERSION-OS.tar.gzln -s full-path-to-mysql-VERSION-OS mysqlcd mysqlmkdir mysql-fileschmod 770 mysql-fileschown -R mysql .chgrp -R mysql .bin/mysql_install_db --user=mysql    # Before MySQL 5.7.6bin/mysqld --initialize --user=mysql # MySQL 5.7.6 and upbin/mysql_ssl_rsa_setup              # MySQL 5.7.6 and upchown -R root .chown -R mysql data mysql-filesbin/mysqld_safe --user=mysql &cp support-files/mysql.server /etc/init.d/mysql.server

环境变量问题：    在环境变量中设置一个MySQL_HOME变量    把MySQL的bin目录加入到PATH中    加入一个更改MySQL客户端提示符的变量#cat .bash_profileexport MYSQL_HOME=/usr/local/mysql/PATH=$PATH:$HOME/bin:$MYSQL_HOME/binexport MYSQL_PS1="(\u@\h) [\d]\>"export PATH

 

MySQL的相关操作

----查看本会话的连接信息

(root@localhost) [(none)]>\s

--------------

mysql Ver 14.14 Distrib 5.7.10, for linux-glibc2.5 (x86_64) using EditLine wrapper

Connection id: 24

Current database:

Current user: root@localhost

SSL: Not in use

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server version: 5.7.10 MySQL Community Server (GPL)

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: latin1

Db characterset: latin1

Client characterset: utf8

Conn. characterset: utf8

UNIX socket: /tmp/mysql.sock

Uptime: 5 hours 15 min 42 sec

Threads: 1 Questions: 3021 Slow queries: 0 Opens: 340 Flush tables: 1 Open tables: 87 Queries per second avg: 0.159

--------------

(root@localhost) [(none)]>

--查看mysql使用参数文件的方式[root@zhenxing ~]# mysql --help|grep my.cnforder of preference, my.cnf, $MYSQL_TCP_PORT,/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf /usr/local/mysql/my.cnf ~/.my.cnf默认原则是排在后面的参数文件参数覆盖前面参数文件中指定的参数(也就是如果存在/etc/my.cnf 和~/.my.cnf中存在同样的参数不同的值，则以~/.my.cnf中指定的为准)

 

课堂问题:

如果使自增列插入0值？(默认自增列插入NULL或插入0都表示自增列字段以1为起点向上自增)通过修改MySQL的SQL_MODE来插入自增列为0的值(NO_AUTO_VALUE_ON_ZERO)

mysql>ALTER SQL_MODE='NO_AUTO_VALUE_ON_ZERO';--该参数表示改变默认0的行为   下列是官方文档说明NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, yougenerate the next sequence number for the column by inserting either NULL or 0 into it.NO_AUTO_VALUE_ON_ZERO suppresses(抑制) this behavior for 0 so that only NULL generates the nextsequence number.This mode can be useful if 0 has been stored in a table's AUTO_INCREMENT column. (Storing 0is not a recommended practice, by the way.) For example, if you dump the table with mysqldumpand then reload it, MySQL normally generates new sequence numbers when it encounters the0 values, resulting in a table with contents different from the one that was dumped. EnablingNO_AUTO_VALUE_ON_ZERO before reloading the dump file solves this problem. mysqldump nowautomatically includes in its output a statement that enables NO_AUTO_VALUE_ON_ZERO, to avoidthis problem. 如果将0保存到表的AUTO_INCREMENT列，该模式会很有用。(不推荐采用该方式)。例如，如果你用mysqldump转储表并重载入，MySQL 遇到0值一般会生成新的序列号，生成的表的内容与转储的表不同。但也可在重载转储文件前启用NO_AUTO_VALUE_ON_ZERO可以解决该问题。

 

将SQL_MODE还原为默认值

每个版本的MySQL的SQL_MODE的默认值都不同,可以搜索MySQL的参考文档查看对应版本的SQL_MODE默认值--修改成5.7.10的SQL_MODE默认值mysql>SET SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

如何通过sql区分出char和varchar的不同?

(root@localhost) [test]>create table t(col1 char(10),col2 varchar(10));  --创建测试表Query OK, 0 rows affected (0.03 sec)(root@localhost) [test]>desc t;   --查看表结构+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| col1  | char(10)    | YES  |     | NULL    |       || col2  | varchar(10) | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)(root@localhost) [test]>insert into t(col1,col2) values('abc','abc');  --插入测试数据Query OK, 1 row affected (0.00 sec)(root@localhost) [test]>select col1,length(col1) as 'length(col1)',char_length(col1) 'char_length(col1)',col2,length(col2) as 'length(col2)',char_length(col2) as 'char_length(col2)' from t;   --查看字段长度,都显示为3+------+--------------+-------------------+------+--------------+-------------------+| col1 | length(col1) | char_length(col1) | col2 | length(col2) | char_length(col2) |+------+--------------+-------------------+------+--------------+-------------------+| abc  |            3 |                 3 | abc  |            3 |                 3 |+------+--------------+-------------------+------+--------------+-------------------+1 row in set (0.00 sec)(root@localhost) [test]>SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';   --设置SQL_MODE参数Query OK, 0 rows affected, 1 warning (0.00 sec)  --存在一个警告(root@localhost) [test]>show warnings;  --查看警告显示该参数可能会在未来版本中被废弃,需要注意+---------+------+------------------------------------------------------------------------------------------------+| Level   | Code | Message                                                                                        |+---------+------+------------------------------------------------------------------------------------------------+| Warning | 3090 | Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. |+---------+------+------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)(root@localhost) [test]>select col1,length(col1) as 'length(col1)',char_length(col1) 'char_length(col1)',col2,length(col2) as 'length(col2)',char_length(col2) as 'char_length(col2)' from t;    --再次查看字段长度,char变成了10，而varchar仍旧是3+------------+--------------+-------------------+------+--------------+-------------------+| col1       | length(col1) | char_length(col1) | col2 | length(col2) | char_length(col2) |+------------+--------------+-------------------+------+--------------+-------------------+| abc        |           10 |                10 | abc  |            3 |                 3 |+------------+--------------+-------------------+------+--------------+-------------------+1 row in set (0.00 sec) 官方说明：

By default, trailing spaces are trimmed from CHAR column values on retrieval. If PAD_CHAR_TO_FULL_LENGTH is enabled, trimming does not occur and retrieved CHAR values are padded to their full length. This mode does not apply to VARCHAR columns, for which trailing spaces are retained on retrieval.

解释：默认检索数据时会把char的后空格trim(修剪,截断)掉,所以需要使SQL_MODE的PAD_CHAR_TO_FULL_LENGTH参数来禁用该默认规则。

 

MySQL5.7.x使用workbench连接数据库的问题

在MySQL5.6之前,数据库是默认不开启ssh加密传输方式的，在5.7以后，默认安装时执行了ssh_setup操作进行开启,所以在5.6之前可以默认使用workb的Standard(TCP/IP)协议进行连接，但5.7以后需要使用Standard(TCP/IP) over ssh方式连接，不然会连接报错。