mysql不进库查询 mysql数据插不进去

  本文记录mysql服务器的安装，navicat连接mysql，及最终代码连接数据库并插入数据的过程。因为期间遇到了较多问题，因此记录下来。

步骤1： 安装mysql软件

步骤2： navicat连接数据库

步骤3：代码连接并插入数据

 

步骤1： 安装mysql软件 

    1.1 下载地址： https://dev.mysql.com/downloads/mysql/

    1.2  解压缩包，在解压后的文件夹根目录创建my.ini配置文件

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
 
[mysqld]
# 设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=C:\\web\\mysql-8.0.11
# 设置 mysql数据库的数据的存放目录，MySQL 8+ 不需要以下配置，系统自己生成即可，否则有可能报错
# datadir=C:\\web\\sqldata
# 允许最大连接数
max_connections=20
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

    1.3 初始化数据库。用超管身份打开cmd，先跳转到mysql目录的bin文件夹(注意：不同分区的cd命令，需要加/d才能生效。例如  cd /dD:\)。

         执行mysqld --initialize --console初始化数据库.执行报错

     

D:\software\mysql-8.0.16-winx64\bin>mysqld --initialize --console
mysqld: Can't create directory 'D:\software\mysql-8.0.16-winx64\bin\ oftware\mysql-8.0.16-winx64\data\' (OS errno 2 - No such file or directory)
2019-07-02T10:42:20.497534Z 0 [System] [MY-013169] [Server] D:\software\mysql-8.0.16-winx64\bin\mysqld.exe (mysqld 8.0.16) initializing of server in progress as process 15048
2019-07-02T10:42:20.497604Z 0 [ERROR] [MY-010338] [Server] Can't find error-message file 'D:\software\mysql-8.0.16-winx64\bin\ oftware\mysql-8.0.16-winx64\share\errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
2019-07-02T10:42:20.498107Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2019-07-02T10:42:20.499339Z 0 [ERROR] [MY-013236] [Server] Newly created data directory D:\software\mysql-8.0.16-winx64\bin\ oftware\mysql-8.0.16-winx64\data\ is unusable. You can safely remove it.
2019-07-02T10:42:20.500631Z 0 [ERROR] [MY-010119] [Server] Aborting
2019-07-02T10:42:20.500855Z 0 [System] [MY-010910] [Server] D:\software\mysql-8.0.16-winx64\bin\mysqld.exe: Shutdown complete (mysqld 8.0.16)  MySQL Community Server - GPL.

我的my.ini配置文件如下：

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
 
[mysqld]
# 设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=D:\software\mysql-8.0.16-winx64
# 设置 mysql数据库的数据的存放目录，MySQL 8+ 不需要以下配置，系统自己生成即可，否则有可能报错
#datadir=D:/software/mysql-8.0.16-winx64/dataDir
# 允许最大连接数
max_connections=1000
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

仅仅是修改了basedir的路径而已。

分析报错信息：  Can't create directory 'D:\software\mysql-8.0.16-winx64\bin\ oftware\mysql-8.0.16-winx64\data\' (OS errno 2 - No such file or directory)  发现路径原本是\software变成\ oftware。分析是\s被转义了，因此加入转义符basedir=D:\\software\\mysql-8.0.16-winx64.

再次执行，成功。在mysql目录下多了一个文件夹用于存放数据库

执行结束之后，控制台显示消息

D:\software\mysql-8.0.16-winx64\bin>mysqld --initialize --console
2019-07-02T10:46:48.238967Z 0 [System] [MY-013169] [Server] D:\software\mysql-8.0.16-winx64\bin\mysqld.exe (mysqld 8.0.16) initializing of server in progress as process 13980
2019-07-02T10:46:48.240508Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2019-07-02T10:47:07.715158Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: dwgi7Esr<U7f
2019-07-02T10:47:14.881947Z 0 [System] [MY-013170] [Server] D:\software\mysql-8.0.16-winx64\bin\mysqld.exe (mysqld 8.0.16) initializing of server has completed

注意用户信息及密码信息，记录下来root@localhost: dwgi7Esr<U7f

 1.4  执行安装命令安装服务

mysqld install

1.5  启动服务

  net start mysql     如需关闭，使用net stop mysql

 

步骤2： navicat连接数据库

 结果连接报错。

 

客户端不支持身份校验。查阅之后，需要修改用户的身份校验。步骤如下

     (1)  mysql -u root -p回车之后输入初始化时给的临时密码，再回车

           USE mysql;  

           结果这边提示需要You must reset your password using ALTER USER statement before executing this statement. 所以先跳到第二步修改密码。再回到这边继续执行

           ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '密码';

           FLUSH PRIVILEGES;  (刷新)   执行之后，navicat成功连接mysql。

     (2) alter user user() identified by "123456";

 

步骤3： 代码连接数据库并插入数据

    3.1 使用navicat建数据库，再建表

  

     3.2 代码编写

   

public void insertUser() {
        String sql = "insert into t_user (id, name, phone) values(?, ?, ?)";
        String id = UUID.randomUUID().toString().substring(0, 32);

        jdbcTemplate.update(sql, id, "李四", "4564654");

        System.out.println("插入完成");
    }

注意自己配置Datasource及Jdbctemplate。并加入Spring容器中。

结果执行插入的时候报错

Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception: 
java.sql.SQLException: Unknown system variable 'tx_isolation'

网上查阅之后说是jar包版本的问题。

<dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>4.3.12.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.35</version>
        </dependency>

        <dependency>
            <groupId>c3p0</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.1.2</version>
        </dependency>

将mysql-connector-java版本修改成下载的mysql的版本8.0.16.

重新执行又报错

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.

java.sql.SQLException: The server time zone value '�й���׼ʱ��' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

按第一个提示把驱动类修改了，发现并不是那么一回事。注意还有第二个提示。大意是要配置time zone时间区域。百度之后对数据库连接地址作如下修改

jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC

加入时间区，并设置编码，防止错误信息乱码。再次执行，数据成功插入。