# 数据库中数据表名获取 sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '数据库'"
mysql数据库去重:
1 INSERT into 100158_1 #(表名)2 SELECT DISTINCT(use_id),userName,birth,gender,country,province,city,logTime_last,term_id,course_id,timeChoice #(字段)3 FROM `100158`#原表4 GROUP BY use_id #分组依据
mysql记录出现次数查询:
SELECT user_id ,COUNT(DISTINCT(current_session)) FROM `表名` GROUP BY user_idSELECT t.id ,COUNT(t.id) FROM (SELECT user_id ,COUNT(DISTINCT(sid)) AS id FROM `表名` GROUP BY user_id) t GROUP BY t.id
数据块过大问题:1206, 'The total number of locks exceeds the lock table size'的解决方法:
修改Innodb_Buffer_Pool_size,MySql57版本无需重启数据库。修改default文件不能解决问题,具体方法如下:
进入数据库:
1 net start mysql57; 2 mysql -hlocalhost -uroot -p; use DB;
查看相关属性设置命令:
1 show variables like "%_buffer_%";
修改设置:
SET GLOBAL innodb_buffer_pool_size=400000000 #4G左右
一般修改后就可以进行大数据块的操作,无需继续修改innodb_buffer_pool_instances和innodb_buffer_pool_chunk_size
chunk的大小:innodb_buffer_pool_chunk_size=innodb_buffer_pool_size / innodb_buffer_pool_instances
远程访问权限问题:root默认只有本机访问的权限,要通过其他机器访问,必须授权;进入mysql>:
GRANT ALL PRIVILEGES ON *.* TO'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;flush privileges;