Category: mysql问题

本地网站连接本地mysql  ，ssl是支持的不会报错

jdbc.url=jdbc:mysql://localhost:3306/ssm–crud?useUnicode=true&characterEncoding=UTF-8&useSSL=true

本地网站连接别的主机的mysql时，出现报错：

jdbc.url=jdbc:mysql://112.112.112.112:3306/ssm–crud?useUnicode=true&characterEncoding=UTF-8&useSSL=true

The last packet successfully received from the server was 764 milliseconds ago.  The last packet sent successfully to the server was 757 milliseconds ago.] with root cause

sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

at sun.security.provider.certpath.SunCertPathBuilder.build(SunCertPathBuilder.java:141)

mysql  useSSL=true 问题，因为安装 mysql的时候，它会在本机 导入本机mysql的证书，所以可以使用 ssl 连接本机mysql。但是当本机网站需要连接到别的主机时，本机没有对方的ssl证书，所以就报错了。

解决办法就是：去掉 &useSSL=true

jdbc.url=jdbc:mysql://112.112.112.112:3306/ssm–crud?useUnicode=true&characterEncoding=UTF-8

Tue Nov 06 15:51:39 CST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

一、数据库中文乱码情况

对于mysql数据库的乱码问题，有两种情况：

1. mysql数据库编码问题（建库时设定）。
2. 连接mysql数据库的url编码设置问题。

对于第一个问题，目前个人发现只能通过重新建库解决，建库的时候，选择UTF-8字符集。我试过修改现有数据库字符集为UFT-8，但是根本不起作用，插入的中文仍然乱码（中文显示成：???）。重建库时选择字符集为UTF-8之后，中文正常显示了。

补充：
第一个问题，可以单独的修改表的字符集，更可以单独的修改字段的字符集。 ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name

对于第二个问题，是这样的情况：我建库时设置了数据库默认字符集为UTF-8，通过mysql workbench直接插入中文显示完全正常。但是使用mybaits插入数据时，中文显示成了”???”这样的乱码。但从数据库获取的中文不会乱码。跟踪数据库操作，SQL语句中的中文还是显示正常的，但是插入到mysql数据库后就乱码了，于是判断可能是数据库连接的问题。后来在网上找了下资料，发现确实可以为mysql数据库的连接字符串设置编码方式，如下：

jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8

添加了useUnicode=true&characterEncoding=utf8参数之后，插入中文就正常了。

添加的作用是：指定字符的编码、解码格式。

例如：假设mysql数据库用的是GBK编码（也可能是其它，例如Ubuntu下就是latin1），而项目数据库用的是utf-8编码。这时候如果添加了useUnicode=true&characterEncoding=UTF-8 ，那么作用有如下两个方面：

1. 存数据时：

数据库在存放项目数据的时候会先用UTF-8格式将数据解码成字节码，然后再将解码后的字节码重新使用GBK编码存放到数据库中。

2.取数据时：

在从数据库中取数据的时候，数据库会先将数据库中的数据按GBK格式解码成字节码，然后再将解码后的字节码重新按UTF-8格式编码数据，最后再将数据返回给客户端。

注意：在xml配置文件中配置数据库utl时，要使用&的转义字符也就是&

  例如：<property name="url" value="jdbc:mysql://localhost:3306/email?useUnicode=true&amp;characterEncoding=UTF-8" />

二、HTML中常用的特殊字符：

1、最常用的字符实体(Character Entities)

2、其他常用的字符实体(Character Entities)

三、mysql url连接属性

参考：
https://blog.csdn.net/zht666/article/details/8955952
https://blog.csdn.net/afgasdg/article/details/6941712
https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html

一、数据库存储过程举例：

1、搭建数据库

id      last_name    gender      email
1	  mike	       0         [email protected]	
2	  book	       0  	[email protected]	
3	  tom	       1        [email protected]	
4	  jerry	       1	[email protected]	
5	  hhee	       1	[email protected]	
6	  jerry4       1	[email protected]	
7	  smith0x1     1	[email protected]	
8	  mas	       1	[email protected]	
9	  smith0x1     1	[email protected]	
10	  allen0x1     0	[email protected]

2、创建存储过程

USE `mybatis`;
DROP PROCEDURE IF EXISTS proc_employee;
DELIMITER //
CREATE PROCEDURE proc_employee(IN p_start INT, IN p_end INT,OUT p_count INT)
BEGIN
  SELECT COUNT(*) INTO p_count FROM `tbl_employee`;
  SELECT * FROM ( SELECT a.*  FROM `tbl_employee` a  WHERE a.`id` < p_end)  b
  WHERE b.`id` >p_start;
END//
DELIMITER ;

特别注意点：

开头部分:DELIMITER //【这两斜杠前面有空格，需要注意】
结尾部分：DELIMITER ;【分号前面也有个空格，需要注意】

在定义过程时，使用DELIMITER // 命令将语句的结束符号从分号 ; 
临时改为//，使得过程体中使用的分号被直接传递到服务器，而不会被客户端（如mysql）解释。

3、mysql客户端调用存储过程

set @p_count=1;
CALL proc_employee(1,8,@p_count); 
select @p_count;

二、数据库操作举例

-- 8-1: Stored Procedure Intro
-- http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1_appe.html
 
-- stored procedures
-- paste this script into the mysql client
 
USE test;
DELIMITER |                      -- // permit semi-colons within SP
 
DROP PROCEDURE IF EXISTS worldmesgproc |
CREATE PROCEDURE worldmesgproc( IN s CHAR( 10 ) )
SELECT CONCAT_WS( ' ', s, 'world!' );
|
 
DROP FUNCTION IF EXISTS worldmesgfunc |
CREATE FUNCTION worldmesgfunc( s CHAR( 10 ) ) RETURNS CHAR( 20 )
RETURN CONCAT_WS( s, 'world!' );
|
 
DELIMITER ;                     -- // restore semi-colon as delimiter
 
CALL worldmesgproc( 'Hello' );
Hello world!
SELECT worldmesgfunc( 'Hello' );
Hello world!
 
CALL worldmesgfunc( 'Hi' );     -- // funcs cannot be called
ERROR 1289 at line 18: PROCEDURE worldmesgfunc does not exist
 
DROP PROCEDURE worldmesgproc;
DROP FUNCTION worldmesgfunc;
 
-- # EOF
 
-- 8-2: DECLARE ... HANDLER in an SP
 
-- # Here,  because DECLARE EXIT HANDLER … instructs MySQL to exit the SP
-- # if  SQLSTATE = 23000, the statement SET @err=-1 never executes. Notice that
-- # in order to be able to mark the end of the SP, the code has to set the delimiter to
-- # something other than a semi-colon; this is best done just before creating the SP,
-- # and reset immediately after. The DELIMITER command does not need a second
-- # terminator.
 
USE test ;
SET @err = 0 ;
SELECT 'Before running errhandlerdemo:', @err ;
CREATE TABLE IF NOT EXISTS testhandler (i INT, PRIMARY KEY(i)) ;
 
DELIMITER |
DROP PROCEDURE IF EXISTS errhandlerdemo ;
CREATE PROCEDURE errhandlerdemo()
  BEGIN
    DECLARE EXIT HANDLER FOR SQLSTATE '23000' SET @err=23000;
    INSERT INTO testhandler VALUES( NULL) ;
    SET @err=-1 ;
  END ;
|
DELIMITER ;
 
CALL errhandlerdemo() |
DROP TABLE testhandler |
SELECT 'After running errhandlerdemo:', @err;
 
-- # EOF
 
-- 8-3:IF...THEN... in an SP
 
USE test;
DROP FUNCTION IF EXISTS is_even;
DELIMITER |
CREATE FUNCTION is_even( x INT ) RETURNS INT
BEGIN
  DECLARE iRet INT DEFAULT 0;
  IF x/2 = 0 THEN
    SET iRet = 1;
  END IF;
  RETURN iRet;
END
|
DELIMITER ;
 
SELECT is_even( 3 );
 
-- #EOF
 
-- 8-4: CASE...WHEN...ENDCASE in an SP
 
USE test;
 
DROP PROCEDURE IF EXISTS case1proc;
DELIMITER |
CREATE PROCEDURE case1proc( IN x INT )
BEGIN
  CASE x
    WHEN 'string' THEN SELECT 'non-matching value will not execute';
    WHEN 0 THEN SELECT 'matching value executes';
    WHEN 17 THEN SELECT 'non-matching value will not execute';
  END CASE;
END;
|
 
DROP PROCEDURE IF EXISTS case2proc |
CREATE PROCEDURE case2proc( IN x INT )
BEGIN
  DECLARE s CHAR( 10 );
  CASE
    WHEN x < 0 THEN SET s = 'less than zero';
    WHEN x < 10 THEN SET s = 'units';
    WHEN x < 100 THEN SET s = 'tens';
    WHEN x < 1000 THEN SET s = 'hundreds';
    ELSE SET s = 'a thousand or more';
  END CASE;
  SELECT CONCAT( 'range is ', s );
END;
|
 
DELIMITER ;
 
CALL case1proc( 0 );           -- # output: matching value executes
CALL case2proc( 100 );         -- # output: range is hundreds
 
-- # EOF
 
-- 8-5: DO .. REPEAT
 
DELIMITER |
 
DROP PROCEDURE IF EXISTS dorepeat;
CREATE PROCEDURE dorepeat( IN imax INT )
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE s CHAR( 20 ) DEFAULT 'Loops:';
REPEAT
BEGIN
SET s = CONCAT_WS( ' ', s, i );
SET i = i + 1;
END;
UNTIL i > imax END REPEAT;
SELECT s;
END
|
 
DELIMITER ;
CALL dorepeat( 5 );
 
-- #EOF
 
-- 8-6: DO ... WHILE ...
 
USE test;
 
DROP PROCEDURE IF EXISTS whileproc;
DELIMITER |
CREATE PROCEDURE whileproc( IN x INT )
BEGIN
   DECLARE i INT DEFAULT 0;
   DECLARE s CHAR( 20 ) DEFAULT "Loops:";
   WHILE i-5 DO
     BEGIN
       SET i = i + 1;
       SET s = CONCAT_WS( ' ', s, i );
     END;
   END WHILE;
   SELECT s;
END;
|
 
DELIMITER ;
 
CALL whileproc( 5 ); -- output: Loops: 1 2 3 4 5
 
-- #EOF
 
-- 8-7: Cursor, handler, loop and iteration in an SP
 
USE test;
DROP TABLE IF EXISTS curtest1;
DROP TABLE IF EXISTS curtest2;
CREATE TABLE curtest1( i INT PRIMARY KEY, j INT, name CHAR(10) );
CREATE TABLE curtest2( x INT );
INSERT INTO curtest1 VALUES (0,0,'first'),(1,2,'second'),(2,1,'third');
 
DROP PROCEDURE IF EXISTS cursxmpl;
DELIMITER |
CREATE PROCEDURE cursxmpl()
BEGIN
  DECLARE mi INT;
  DECLARE mj INT;
  DECLARE done INT DEFAULT 0;
  DECLARE curs CURSOR FOR SELECT i, j FROM test.curtest1;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
 
  OPEN curs;
  loop0: WHILE NOT done DO
    FETCH curs INTO mi, mj;
    IF NOT done THEN
       IF mi < mj THEN
          INSERT INTO test.curtest2 VALUES (mi);
       ELSEIF mi > mj THEN
          INSERT INTO test.curtest2 VALUES (mj);
       ELSE
          ITERATE loop0;
       END IF;
    END IF;
  END WHILE loop0;
  CLOSE curs;
END;
|
DELIMITER ;
CALL cursxmpl();
SELECT * FROM curtest2;
 
-- # EOF

mysql -u root -p 解释