truncate table 表名; truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger. delete from 表名 where 表达式 delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;如果有相应的trigger, 执行的时候将被触发.
原因： 同一个ip在短时间内产生太多（超过mysql数据库max_connection_errors的最大值）中断的数据库连接而导致的阻塞 解决办法：1、提高允许的max_connection_errors数量（治标不治本） 2、使用mysqladmin flush-hosts 命令清理一下hosts文件（不知道mysqladmin在哪个目录下可以使用命令查找：whereis mysqladmin）； 参考文档: http://www.cnblogs.com/susuyu/archive/2013/05/28/3104249.html
show create table tablename; show create view viewname; show create procedure proc_name; show create event event_name;
SHOW VARIABLES LIKE ‘event_scheduler’; – 开启计划任务 SET GLOBAL event_scheduler = ON;
– 若已存在则删除 drop event if exists `call_report_yesterday`;
– 创建 CREATE EVENT if not exists `call_report_yesterday` ON SCHEDULE EVERY 1 DAY STARTS date_add(curdate(), interval 26 hour) – 第二天02点开始 ON COMPLETION NOT PRESERVE ENABLE COMMENT ‘每天02点 统一执行报表统计存储过程，统计前一天数据’ DO CALL report_yesterday();
CASE input_expression WHEN when_expression THEN result_expression [ …n ] ELSE else_result_expression END
– 更新 don_users_collect 表 添加elder_id,type 字段数据 update don_users_collect set `type` = ‘1’, elder_id = (select elder_id from don_project where don_users_collect.project_id = don_project.id);
delete cp from colum_projec cp left join projec p on cp.project_id = p.id where cp.column_id = ‘3954e30538464ec69f55350d38ea20a0’ and p.status = ‘4’;
DML（data manipulation language）： 它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言 DDL（data definition language）： DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用 DCL（Data Control Language）： 是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL
四舍五入 round(column, 2)
当使用 UNION 时，MySQL 会把结果集中重复的记录删掉，而使用 UNION ALL ，MySQL 会把所有的记录返回，且效率高于 UNION
SQL 错误 [S1000]: User does not have access to metadata required to determine stored procedure parameter types. If rights can not be granted, configure connection with “noAccessToProcedureBodies=true” to have driver generate parameters that represent INOUT strings irregardless of actual parameter types. java.sql.SQLException: User does not have access to metadata required to determine stored procedure parameter types. If rights can not be granted, configure connection with “noAccessToProcedureBodies=true” to have driver generate parameters that represent INOUT strings irregardless of actual parameter types.
1.show grants for username 查看用户权限 grant all privileges on on ‘database’.* to ‘username’@’%’ with grant option 猜测应该有权限 2.通过root账户查看该存储过程，发现该存储过程的 definer 为 ‘root’@’%’ 修改definer 为 ‘username’@’%’ 切换username账号，可以查看修改 存储过程了 3.参考文档 http://yunjiechao-163-com.iteye.com/blog/1300772
mysql version 5.7.15-log sql1: update ( select ids,(@rowNum := @rowNum + 1) as rownum from don_activity a, ( select ( @rowNum := 0 ) ) r where a.`type` = ? order by a.sort asc ) t1, don_activity t2 set t2.sort = t1.rownum where t2.`type` = ? and t1.ids = t2.ids 手动更改 don_activity 表中的 sort 字段，执行sql1 ，sort 字段将恢复到手动更改之前的排序 sql2 update ( select * from ( select ids,(@rowNum := @rowNum + 1) as rownum from don_activity a, ( select ( @rowNum := 0 ) ) r where a.`type` = ? order by a.sort asc ) t ) t1, don_activity t2 set t2.sort = t1.rownum where t2.`type` = ? and t1.ids = t2.ids 同样操作，执行sql2 后能正常更新sort 字段。 而在 5.6.16-log中sql1 也能正常更新 sort 字段
参考地址：http://blog.csdn.net/zhuxineli/article/details/14455029
参看网址：http://blog.csdn.net/jesseyoung/article/details/35257527
ALTER [DEFINER = { user | CURRENT_USER }] EVENT event_name [ON SCHEDULE schedule] [ON COMPLETION [NOT] PRESERVE] [RENAME TO new_event_name] [ENABLE | DISABLE | DISABLE ON SLAVE] [COMMENT ‘comment’] [DO event_body]
修改为不活动 alter event event_name disable; 修改为活动 alter event event_name enable;
drop event [if exists] event_name;
参考网址：http://blog.csdn.net/llmlx/article/details/7662497
ENABLED 激活态，符合条件将执行 DISABLED 禁用态，不会执行 SLAVESIDE_DISABLED 从库上不执行该EVENT，该状态应用在主库还是从库？若只有一个库，并且库是该状态事件会执行吗？
不会，没回导库必须重新创建事件
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect. Solution: SET SQL_SAFE_UPDATES = 0;
create table table_name_bak as select * from table_name;
- Fetch time - measures how long transferring fetched results take, which has nothing to do with query execution. I would not consider it as sql query debugging/optimization option since fetch time depends on network connection, which itself does not have anything to do with query optimization. If fetch time is bottleneck then more likely there’s some networking problem. Note: fetch time may vary on each query execution.
- Duration time - is the time that query needs to be executed. You should try to minimize it when optimizing performance of sql query.
update table_name a join table_name b on a.b_id = b.id set a.col1 = b.col1, a.col2 = b.col2;
- test
varchar(20) decimal(11,2) result 27600 27600.00 success null null success 27600元 27600.00 525 row(s) affected, 1 warning(s): 1292 Truncated incorrect DECIMAL value: ‘27600元’ Records: 525 Duplicates: 0 Warnings: 1 27元600元 27.00 525 row(s) affected, 1 warning(s): 1292 Truncated incorrect DECIMAL value: ‘27元600元’ Records: 525 Duplicates: 0 Warnings: 1 27e600元 999999999.99 525 row(s) affected, 3 warning(s): 1292 Truncated incorrect DECIMAL value: ‘27’ 1292 Truncated incorrect DECIMAL value: ‘27e600元’ 1264 Out of range value for column ‘amount’ at row 1 Records: 525 Duplicates: 0 Warnings: 3 27a600 27.00 525 row(s) affected, 1 warning(s): 1292 Truncated incorrect DECIMAL value: ‘27a600’ Records: 525 Duplicates: 0 Warnings: 1 27.600.123 27.60 525 row(s) affected, 1 warning(s): 1292 Truncated incorrect DECIMAL value: ‘27.600.123’ Records: 525 Duplicates: 0 Warnings: 1 27.612 27.61 525 row(s) affected, 1 warning(s): 1265 Data truncated for column ‘amount’ at row 1 Records: 525 Duplicates: 0 Warnings: 1 27.616 27.62 525 row(s) affected, 1 warning(s): 1265 Data truncated for column ‘amount’ at row 1 Records: 525 Duplicates: 0 Warnings: 1 27.61.6 27.61 525 row(s) affected, 1 warning(s): 1292 Truncated incorrect DECIMAL value: ‘27.61.6’ Records: 525 Duplicates: 0 Warnings: 1 - 结论
- null 不变
- 从左到右，截取正确数字格式的字符串转换为decimal
- 小数位数超出部分做四舍五入进位
- 原因
导出时自动生成如下语句
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN; SET @@SESSION.SQL_LOG_BIN= 0; -- -- GTID state at the beginning of the backup -- SET @@GLOBAL.GTID_PURGED='6be07c38-0ec2-11e7-af4e-00163e03b936:1-1004797'; SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;这些语句的执行需要有 SUPER privilege 权限
- 关于 sql_log_bin 参考地址：https://dev.mysql.com/doc/refman/5.5/en/set-sql-log-bin.html
- 解决办法
- 导出文件中直接删除语句
- 修改 advanced options set-gtid-purged… 由AUTO或ON 修改为 OFF
- 通过游标遍历表，为每一条数据生成一定时间范围内的随机时间作为创建时间
CREATE DEFINER=`root`@`%` PROCEDURE `test`() BEGIN # 遍历数据结束标志 declare done int default false; # 定义参数表示执行结果 declare my_error int default 0; # 定义游标 declare mycursor cursor for select id from nursinghome_bak_2017_10_31; # 将结束标志绑定到游标 declare continue handler for not found set done = true; # 事务执行失败返回1 declare continue handler for sqlexception set my_error = 1; declare temp_id int default 0; # 最小时间 declare timeStart bigint default unix_timestamp('2015-01-01'); # 最大时间 declare timeEnd bigint default unix_timestamp('2016-01-01'); # 开始事务 start transaction; # 打开游标 open mycursor; fetch mycursor into temp_id; repeat if not done then update nursinghome_bak_2017_10_31 set create_time = from_unixtime(timeStart + floor(rand() * (timeEnd - timeStart))) where id = temp_id; end if; fetch mycursor into temp_id; until done end repeat; # 关闭游标 close mycursor; # 事务判断 if my_error then # 事务回滚 rollback; else # 事务提交 commit; end if; # 返回事务执行结果 select my_error as result; END - 游标嵌套
CREATE DEFINER=`root`@`%` PROCEDURE `test`() BEGIN # 遍历数据结束标志 declare done int default false; # 定义参数表示执行结果 declare my_error int default 0; declare report_day_temp varchar(20); declare nursinghome_id_temp int default 0; declare living_num_temp int default 0; # 定义游标 declare out_cursor cursor for select id from table_a where temp_status = 1; declare in_cursor cursor for select report_day, living_num from table_b where report_day > '2017-03-01' and nursinghome_id = 1; # 将结束标志绑定到游标 declare continue handler for not found set done = true; # 事务执行失败返回1 declare continue handler for sqlexception set my_error = 1; start transaction; open out_cursor; fetch out_cursor into nursinghome_id_temp; repeat if not done then open in_cursor; fetch in_cursor into report_day_temp,living_num_temp; while done <> 1 do insert into report_day_nursing_living( report_day,nursinghome_id,living_num ) values( report_day_temp, nursinghome_id_temp, living_num_temp + floor(rand()*5)); fetch in_cursor into report_day_temp,living_num_temp; end while; close in_cursor; set @done = 0; end if; fetch out_cursor into nursinghome_id_temp; until done end repeat; close out_cursor; if my_error then rollback; else commit; end if; select my_error as result; END
mysql的collation大致的意思就是字符序。首先字符本来是不分大小的，那么对字符的>, = , < 操作就需要有个字符序的规则。