MySQL 教程 在线

2550MySQL ALTER命令

Specified key was too long; max key length is 767 bytes

mysq索引的字段都太长了

解决办法一

让mysql支持 长索引 插入表时 添加ROW_FORMAT=DYNAMIC  自动格式化索引

数据库层面的修改

show variables like 'innodb_large_prefix';  

show variables like 'innodb_file_format';

 --修改最大索引长度限制

set global innodb_large_prefix=1;

set global innodb_file_format=BARRACUDA;

-- 添加

set global innodb_file_format_max=BARRACUDA;

建表语句的修改(直接使用navicat就不需要了)

-- 修改插入sql的语句添加ROW_FORMAT=DYNAMIC

create table idx_length_test_02

(

  id int auto_increment primary key,

  name varchar(255)

)

ROW_FORMAT=DYNAMIC default charset utf8mb4;

解决办法二

不使用utf8mb4格式如

author_id 修改为 字符集utf8 排序规则 utf8_general_ci

2510MySQL 函数

需求:将数据库中每分钟一条的数据表,从 9:30 取到 22:00 ,以半小时为单位汇总,并输出 Excel。

数据表字段:id(序号)、incount(计数)、cdate(数据时间)

表名:m_temp

难点:时间处理

解决办法:使用 DATE_FORMAT、CONCAT、Date、Hour、Minute、Floor 函数将时间处理成半小时,在将这段时间的数据查询时 输出 cdate 统一变为  09:30,然后在 group by cdate ,用 sum(incount)求和。

1.处理时间:

示例:2018-08-21 09:30:00   至 2018-08-21 22:00:00

第一个半小时 09:30 至 10:00

将这段时间的数据查询时 输出 cdate 统一变为 09:30,然后在 group by cdate ,sum(incount)

将分钟取出 /30  , 0-29 分结果为 0 ,30-59分结果为 1 再乘以 30 分钟即变为 00 或者 30

date(cdate),取出年月日;

hour(cdate),取出小时;

minute(cdate),取出分钟;

计算(minute(cdate))/30 )*30,结果为 0 或30;

floor( (minute(cdate))/30 )*30), 处理成整数;

concat(date(cdate),' ',hour(cdate),':',floor( (minute(cdate))/30 )*30) ,按照日期格式进行拼接

DATE_FORMAT( concat(date(cdate),' ',hour(cdate),':',floor( minute(cdate)/30 )*30+12) ,'%Y-%m-%d %H:%i') ,转换成date类型

完整 SQL:

select sum(incount),dataStartTime from (
select incount, DATE_FORMAT( concat(date(cdate),' ',hour(cdate),':',floor( minute(cdate)/30 )*30+12) ,'%Y-%m-%d %H:%i')  as dataStartTime   
 from m_temp WHERE cdate>='2018-08-21 09:30' and cdate<='2018-08-21 22:00'  ORDER BY dataStartTime
) a
group by DATE_FORMAT( dataStartTime ,'%Y-%m-%d %H:%i')
into outfile('c:/2018-08-21Data.xls');

2509MySQL 导出数据

在写出的时候会出现The MySQL server is running with the --secure-file-priv option so it cannot execute this statement的错误解决方法:

出现这个错误是因为没有给数据库指定写出文件的路径或者写出的路径有问题。

首先使用下面的命令 show variables like '%secure%'; 查看数据库的存储路径。如果查出的 secure_file_priv 是 null 的时候就证明在 my.ini 文件里面没有配置写出路径。

这时候就可以在 mysql.ini 文件的 [mysqld] 代码下增加 secure_file_priv=E:/TEST 再重启 mysql 就可以了。然后在导出的地址下面写上刚才配置的这个地址 eg: select * from tb_test into outfile "E:/TEST/test.txt";就可以了。

2508MySQL 导出数据指定主机的数据库

将指定主机的数据库拷贝到本地

如果你需要将远程服务器的数据拷贝到本地,你也可以在 mysqldump 命令中指定远程服务器的IP、端口及数据库名。

在源主机上执行以下命令,将数据备份到 dump.txt 文件中:

请确保两台服务器是相通的:

mysqldump -h other-host.com -P port -u root -p database_name > dump.txt
password ****

2507MySQL 处理重复数据

select 列名1,count(1) as count 
from 表名
group by  列名1
having count>1  and 其他条件

select 列名1,列名2,count(1) as count 
from 表名
group by  列名1,列名2 
having count>1  and 其他条件

原理:先按照要查询出现重复数据的列,进行分组查询。count > 1 代表出现 2 次或 2 次以上。

示例:

/*查询重复数据*/
select serialnum,cdate,count(*) as count 
from m_8_customer_temp_20180820bak 
group by serialnum,cdate having  count>1 and cdate>='2018-08-20 00:00:00';