分类
MySql

MySQL执行查询结果创建新表SQL错误

查询数据,并将查询出来的数据放到新的备份表。使用以下语句:

--导入数据并生成表
select * into 表 from tablename

执行失败,返回:

SQL 错误 [1786] [HY000]: Statement violates GTID consistency: CREATE TABLE … SELECT.

错误原因

这是因为在5.6及以上的版本内,开启了 enforce_gtid_consistency=true 功能导致的,MySQL官方解释说当启用 enforce_gtid_consistency 功能的时候,MySQL只允许能够保障事务安全,并且能够被日志记录的SQL语句被执行,像create table … select 和 create temporarytable语句,以及同时更新事务表和非事务表的SQL语句或事务都不允许执行。

分类
MySql 数据库 笔记

查看MySQL表字段信息

做完表设计后需制作说明文档。我使用MySQL官方客户端MySQL Workbench建模,该工具不支持把字段信息导出文档,只能生成可执行SQL建库建表。

用客户端建表时字段说明,策略都做好了的,重新再写一次太麻烦,所以想着提取这些信息导出文本。

MySQL中常用查看数据库信息的方法:

# 查看当前用户所有楼据库
show databases;
# 查看当前数据库中所有表
show tables;
# 看数表中所有字段信息
desc 表名

使用desc查看字段,并没有显示建库建表的COMMENT备注,字段信息不能完全符合要做的文档。其实可以在information_schema中查找提取信息。

分类
Java MySql

Cause: java.sql.SQLException: Zero date value prohibited

使用MySQL 8.0驱动连接数据库,执行操作,抛出异常:

这是由于createDate字段值引起。该表中某些数据未指定正确的日期。

解决办法:

1.修正数据,指定createDate值

2.在驱动连接加上参数:&zeroDateTimeBehavior=convertToNull

我使用的是第二种方式,驱动url为:

jdbc:mysql://10.243.3.18:3306/gbd?characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8&zeroDateTimeBehavior=convertToNull

旧版驱动没有这个问题,应该是新版驱动增加了更严格的校验。

分类
MySql 数据库 笔记

MySQL多实例启动

一台服务器安装MySQL,用于测试。同时会用于NextCloud和Wordpress的数据库。NextCloud和Wordpress要经常备份,而且以后可能会迁移。所以隔离不同情景的数据,用多实例启动。便于管理,和提高性能。

首先初始化要用到的数据库:

mysqld --initialize-insecure --datadir=/home/mysql/3307/data --user=mysql;
mysqld --initialize-insecure --datadir=/home/mysql/3308/data --user=mysql;
mysqld --initialize-insecure --datadir=/home/mysql/3309/data --user=mysql;

MySQL自带了mysqld_multi工具运行多个实例。以下是我的配置文件:

[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = root
#password = 

# nextcloud
[mysqld3307]
user		= mysql
pid-file	= /home/mysql/3307/mysqld.pid
socket		= /home/mysql/3307/mysqld.sock
port		= 3307
datadir		= /home/mysql/3307/data
lc-messages-dir	= /usr/share/mysql
skip-external-locking
bind-address		= 0.0.0.0
key_buffer_size		= 16M
max_allowed_packet	= 16M
thread_stack		= 192K
thread_cache_size       = 8
myisam-recover-options  = BACKUP
query_cache_limit	= 1M
query_cache_size        = 16M
expire_logs_days	= 10
max_binlog_size   = 100M
innodb_buffer_pool_size = 1G
innodb_io_capacity = 4000

# wordpress
[mysqld3308]
user		= mysql
pid-file	= /home/mysql/3308/mysqld.pid
socket		= /home/mysql/3308/mysqld.sock
port		= 3308
datadir		= /home/mysql/3308/data
lc-messages-dir	= /usr/share/mysql
skip-external-locking
bind-address		= 0.0.0.0
key_buffer_size		= 16M
max_allowed_packet	= 16M
thread_stack		= 192K
thread_cache_size       = 8
myisam-recover-options  = BACKUP
query_cache_limit	= 1M
query_cache_size        = 16M
expire_logs_days	= 10
max_binlog_size   = 100M
innodb_buffer_pool_size = 1G
innodb_io_capacity = 4000
innodb_io_capacity = 4000

[mysqld3309]
user		= mysql
pid-file	= /home/mysql/3309/mysqld.pid
socket		= /home/mysql/3309/mysqld.sock
port		= 3309
datadir		= /home/mysql/3309/data
lc-messages-dir	= /usr/share/mysql
skip-external-locking
bind-address		= 0.0.0.0
key_buffer_size		= 16M
max_allowed_packet	= 16M
thread_stack		= 192K
thread_cache_size       = 8
myisam-recover-options  = BACKUP
query_cache_limit	= 1M
query_cache_size        = 16M
expire_logs_days	= 10
max_binlog_size   = 100M
innodb_buffer_pool_size = 1G
innodb_io_capacity = 4000

配置多实例,每个实例命名为mysqld*,这里我三个实例配置分别对应[mysql3307],[mysql3308],[mysql3309]。

分类
Linux MySql 笔记

Ubuntu Server初始化MySQL的坑

执行MySQL初始化:

mysqld --initialize --datadir=/home/mysql/3307/data --user=mysql

报错,提示不能创建目录:mysqld: Can’t create directory ‘/home/mysql/3307/data/’ (Errcode: 17 – File exists)

建立目录再执行初始化命令,提示没有权限:mysqld: Can’t create directory ‘/home/mysql/3307/data/’ (Errcode: 13 – Permission denied)

该目录已经属于mysql用户,且有读写执行的权限。设置成0777,依然报错。

相同的命令,曾在CentOS上,自己编译的MySQL用过,不应该是命令的问题。该机器是我的笔记本,安装的是Ubuntu Server 18.04,MySQL是用官方的apt源安装的。经过一番查找,发现是一个叫AppArmor的玩意搞鬼。

Ubuntu有个AppArmor,是一个Linux系统安全应用程序,类似于Selinux,AppArmor默认安全策略定义个别应用程序可以访问系统资源和各自的特权,如果不设置服务的执行程序,即使你改了属主属组并0777权限,也是对服务起不到作用。

分类
MySql PHP

一个PhpMyAdmin的https错误

使用docker安装phpmyadmin,启动成功登录,出现提示:服务器和客户端上指示的 HTTPS 之间不匹配。这可能导致 phpMyAdmin 无法正常工作或存在安全风险。请修复您的服务器配置以正确指示 HTTPS。

以前phpmyadmin是直接安装在nginx里的,没有这个问题。现在用nginx做为代理。外部访问使用https,内部用http。

解决方法是修改参数:

$cfg[‘PmaAbsoluteUri’] = ‘https://pma.xxx.com’;

在docker中启动,可以在启动时指定环境参数(该参数在官方文档里没有但是有效):

-e PMA_ABSOLUTE_URI=https://pma.xxx.com

分类
MySql SQL

MySQL把多个查询结果合并JSON

一条主数据有诺干详情记录。这种场景是很常见的。有时候我们需要全部查询出来,按关联关系返回结果。

比如:每条广告发布在多个项目里,现在需要查出前10条广告所在的项目名称。

SELECT info.title,
 CONCAT('[',
  GROUP_CONCAT(
  CONCAT('{projectCode:"', gbp.projectCode, '",'),
  CONCAT('projectName:"', gbp.projectName, '"}')),
  ']') AS projectInfo FROM ghome.ghome_billboard_project AS gbp
  left join ghome.ghome_billboard_info AS info
  ON gbp.billboardId = info.billboardId
  GROUP BY gbp.billboardId
  LIMIT 10

主要是GROUP_CONCAT和CONCAT函数用法。这条sql把广告的项目信息转成json格式放在projectInfo字段。

补充(2019-03-15):

mysql的group_concat默认连接长度为1024字符,其余部分都会被截取丢掉。可以修改配置常量:

SET SESSION group_concat_max_len=102400;

或者全局:

SET GLOBAL group_concat_max_len=102400;

也可以在配置文件上加上:

group_concat_max_len = 102400;

分类
Java MySql

JDBC链接MySQL8的注意事项

需要使用高版本的JDBC驱动,“mysql-connector-java 8”以上版本。

1.JDBC driver 由“com.mysql.jdbc.Driver”改为“com.mysql.cj.jdbc.Driver”。

2.JDBC url为:jdbc:mysql://localhost/rs_report?userSSL=true&useUnicode=true&characterEncoding=UTF8&serverTimezone=GMT

3.出现错误:“Establishing SSL connection withoutserver’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 defaultif explicit option isn’t set. For compliance with existing applications notusing SSL the verifyServerCertificate property is set to ‘false’. You needeither to explicitly disable SSL by setting useSSL=false, or set useSSL=trueand provide truststore for server certificate verification.”

解决方案,在url中加上“userSSL=true”或“userSSL=false”。

4.出现错误:“java.sql.SQLException: The server time zonevalue ‘???ú±ê×??±??’ is unrecognized or represents more than one time zone. Youmust configure either the server or JDBC driver (via the serverTimezoneconfiguration property) to use a more specifc time zone value if you want toutilize time zone support.”

解决方案,在url中加上“serverTimezone=GMT”。

原文:https://blog.csdn.net/woshixlhm/article/details/80540881

分类
MySql SQL 笔记

实现 MySQL Top 函数【原创】

需求:查询数据,根据字段分组,取出分组后每组的前N条记录。
如果是在SQL Server中,可以使用top,取前N条记录。但是在MySQL是不支持的。网上说的比较多的是用limit N,虽然可以取到前N条,但那是分组后的N条,不是每组N条数据,所以不符合需求,排除。还有一种是使用union把多个结果连接起来,这种方法需要提前知道有分多少组,而且不适合分组太多的场景,排除。

我的实现思路:
1.查出分组后的数据。
2.使用原表数据和分组后的数据连接起来。
3.按组生成序列(从0开始)。
4.根据序列编号做为条件,找出前N条数据。

分类
MySql SQL 笔记

从SQL查询结果随机取一条数据

最近工作中会涉及复杂的SQL语句,还是挺有意思的。以前想写没有应用场景,现在正好可以多锻炼锻炼。我觉得在实际开发中,程序代码并不是最难的,最难的还是SQL。SQL逻辑性最强,而且还要很清楚表设计,业务场景。特别是复杂的业条场景下提炼数据,要考虑到功能,性能,是否会锁表。
需求:有一张表保存诺干条广告信息。当APP启动时,根据登录用户所在的区域,随机取出一条广告返回,作为启动页。

数据库:MySQL
刚开始,我的做法是:

select floor(max(startPageId) * rand()) from ghome.ghome_startpage_info where 1=1 and status!='00';

startPageId是自动增长的Integer型
其他where条件不贴出来了。这句意思是,查询符合条件的数据,根据 max 函数找出结果最大的,乘以 rand 函数随机生成的一个小于1的数,再用 floor 去掉小数位。得出一个 randId。