分类目录Mysql

自定mysql
““
@Modifying
@Transactional
有修改数据不要忘记加上

@Query(value =”select product_at,GREATEST(max(p1),max(p2), as max from ks12p where product_plan_id=?1 group BY product_at”,nativeQuery = true)
List<Map<String,Object>> findByKsEveryDayMax(Long product_plan_id);

List<Map<String,Object>> productAtMaxes = ks12pDao.findByKsEveryDayMax(3L);
productAtMaxes.forEach(p -> {
System.out.println(“map.entrySet():”+p.entrySet());
Iterator<Map.Entry<String, Object>> iterator1 = p.entrySet().iterator();
while (iterator1.hasNext()){
System.out.println(“iterator1:”+iterator1.next());
}
});

or

@Query(value = “select new com.xx.xx.CarTraceResult(a.plateNo, a.plateColor, a.typeName, a.parkName, max(a.time), count(a.id)) ” +
” from CarTraceRecordEntity a where a.plateNo = ?1 and a.plateColor = ?2 ” +
“and a.type = ?3 group by a.parkNo order by time desc “)
List queryCarTraceRecord(String plateNo, Integer plateColor, Integer type);

or

public interface ProductAtDayMax {
LocalDate getProductAt();
BigDecimal getMax();
}

@Query(value ="select product_at,GREATEST(max(p1),max(p2), as max from ks12p where product_plan_id=?1 group BY product_at",nativeQuery = true)
List<ProductAtDayMax> findByKsEveryDayMax(Long product_plan_id);

List productAtMaxes = ks12pDao.findByKsEveryDayMax(3L);
productAtMaxes.forEach(productAtDayMax -> {
log.info(“[{}] [{}]”,productAtDayMax.getProduct_at(), productAtDayMax.getMax());
});
““`

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes 解决方案：

1：启用系统变量innodb_large_prefix
注意：光有这个系统变量开启是不够的。必须满足下面几个条件：
1：系统变量innodb_large_prefix为ON
2：系统变量innodb_file_format为Barracuda
3：ROW_FORMAT为DYNAMIC或COMPRESSED

mysql> show variables like '%innodb_large_prefix%';
mysql> set global innodb_large_prefix=on;

show variables like '%innodb_file_format%';
set global innodb_file_format=Barracuda;

对脚本进行修改，添加ROW_FORMAT=DYNAMIC
ALTER TABLE TEST ROW_FORMAT=DYNAMIC;
create table test (........) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

ERROR 1146 (42S02): Table ‘库名.表名’ doesn’t exist

1. 首先退出mysql exit
2. 在命令行界面输入：

mysql_upgrade -uroot -p --force

3. 然后重启mysql即可
   windows重启mysql命令是:
   net stop mysql #关闭mysql
   net start mysql #开启mysql

access denied

vim /etc/my.cnf文件；
[mysqld]
skip-grant-tables
flush privileges;
set password for ‘root’@‘localhost’=password(‘Admin123@qwe’);

sudo systemctl restart mysqld

时间错误

mysql –u root –p
确认时区
show variables like '%time_zone%';
set global time_zone = '+8:00';  ##修改mysql全局时区为北京时间，即我们所在的东8区
flush privileges;  #立即生效

vim /etc/mysql/my.cnf
##在[mysqld]区域中加上
default-time_zone = '+8:00'

修改jdbc url连接数据库参数 添加serverTimezone=Asia/Shanghai参数

sudo docker pull mysql:5.6

sudo docker run -itd -p 3306:3306 --name mysql -v ~/mysql/data:/var/lib/mysql -v ~/mysql/conf:/etc/mysqll/conf.d -v ~/mysql/logs:/var/log/mysql  -e MYSQL_ROOT_PASSWORD=PW123456 --restart always mysql:5.7 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
PS:The default configuration for MySQL can be found in /etc/mysql/my.cnf, which may !includedir additional directories such as /etc/mysql/conf.d or /etc/mysql/mysql.conf.d. Please inspect the relevant files and directories within the mysql image itself for more details.

远程不能访问时需要进入docker本地客户端设置远程访问账号

$ sudo docker exec -it mysql bash
$ mysql -u root -p PW123456
mysql> grant all privileges on *.* to root@'%' identified by "password";

修改MySQL配置文件有两种方法：
一是进入容器，修改容器里的MySQL的配置文件，然后重新启动容器：
docker exec -it mysqlserver /usr/bin/bash
然后可以进入容器的命令行模式，接着修改 /etc/mysql/my.cnf 文件即可
二是挂载主机的mysql配置文件 -v /my/custom:/etc/mysql/conf.d

查看编码

show variables like 'character%';
show variables like 'collation_%';