mysql问题-启用ssl加密连接数据库时报错

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

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

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

jdbc.url=jdbc:mysql://112.112.112.112:3306/ssmcrud?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/ssmcrud?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问题-插入数据库中文乱码

一、数据库中文乱码情况

对于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)

显示结果 说明 Entity Name Entity Number
显示一个空格 &nbsp; &#160;
< 小于 &lt; &#60;
> 大于 &gt; &#62;
& &符号 &amp; &#38;
双引号 &quot; &#34;

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

显示结果 说明 Entity Name Entity Number
© 版权 &copy; &#169;
® 注册商标 &reg; &#174;
× 乘号 &times; &#215;
÷ 除号 &divide; &#247;

三、mysql url连接属性

Properties and Descriptions
useUnicode

Should the driver use Unicode character encodings when handling strings? Should only be used when the driver can’t determine the character set mapping, or you are trying to ‘force’ the driver to use a character set that MySQL either doesn’t natively support (such as UTF-8), true/false, defaults to ‘true’

Default: true

Since version: 1.1g

characterEncoding

If ‘useUnicode’ is set to true, what character encoding should the driver use when dealing with strings? (defaults is to ‘autodetect’)

Since version: 1.1g


参考:
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

mysql问题-存储过程之DELIMITER后面要加空格

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

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问题-命令行连接mysql密码无效

mysql -u root -p 解释

 

使用此命令首先确保你的mysql运行环境已经搭建好

这是客户端连接mysql服务器的指令,比较全的写法是下面两种

第一个是全拼,第二个是第一个的缩写

mysql –host=localhost –user=myname –password=password mydb

mysql -h localhost -u myname -ppassword mydb

一般在使用中,我们会省略-h参数,mysql会自动默认为本地连接

还有一点就是各个参数之间是否有空格的问题,-u后面可以有也可以省略空格,对于-p后面的参数我要单独说一下

这是我从mysql官方文档拷过来的内容

for password options, the password value is optional:

If you use a -p or --password option and specify the password value, there must be no space between -p or --password= and the password following it.

  If you use a -p or --password option but do not specify the password value, the client program prompts you to enter the password. The password is not displayed as you enter it. This is more secure than giving the password on the command line. Other users on your system may be able to see a password specified on the command line by executing a command such as ps auxw

具体就是:对于password选项,此选项是可选的

如果你明确指定了-p或者–password的值,那么-p或者–password和密码值之间是不能有空格的。

如果你使用了-p或者–password选项但是没有给出password值,客户端程序提示您输入密码。

For mysql, the first nonoption argument is taken as the name of the default database. If there is no such option, mysql does not select a default database.

对于MySQL,第一个非选项参数被当作默认数据库的名称。如果没有这样的选项,MySQL就不会选择默认数据库。

也就是说在命令行中,你的mysql密码和-p或者–password参数之间有空格,mysql会认为你输入的是登录mysql后自动选择的数据库,而不是你所期望的密码

当然命令行连接数据库还有其它参数,这里主要介绍几个经常使用的,其它请参考https://dev.mysql.com/doc/refman/5.5/en/connecting.html

来自:https://www.cnblogs.com/phproom/p/9549229.html