SELECT * FROM `XXX` PROCEDURE ANALYSE() \G;
SHOW INDEX FROM `XXX`;
DESC `XXX`;
SET PROFILING=1;
SELECT * FROM `XXX` ORDER BY `id` ASC ;
SELECT * FROM `XXX` ORDER BY `name` ASC ;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;
SHOW PROFILE ALL FOR QUERY 2;
mysqlslap --help;
mysqlslap -a -c 50 -i 5 -uroot -p123456;
mysqlslap --create-schema test -q "SELECT id FROM XXX WHERE type=1" -c 1000 -i 10 -uroot -p123456;
mysqldumpslow
MySQL随机插入某个范围的数字
set @from = 150;
set @to = 200;
SELECT floor(@from+rand() * (@to-@from+1)) as num;
MySQL函数、MySQL存储过程、MySQL建仿真测试表、MySQL批量生成百万、千万、亿级别大数据仿真测试数据
Mysql批量给表字段增加注释
$comment = [
'user' => [
'id' => '主键',
'name' => '姓名'
],
'account' => [
'name' => '姓名',
'type' => '类型'
]
];
$table = 'user';
$data = \Db::queryRow('SHOW CREATE TABLE `'.$table.'`');
$sqlArray = array_map(
function($v)use($table, $comment){
$field = explode('`', $v)[1];
return 'ALTER TABLE `'.$table.'` MODIFY'.rtrim($v, ',').' COMMENT "'.
(isset($comment[$table][$field]) ? addslashes($comment[$table][$field]) : '').'";';
},
array_filter(
preg_split("/\n/isu", $data['Create Table']),
function($v){
return strpos(trim($v), '`') === 0;
}
)
);
var_dump($sqlArray);exit();
MySql 外键约束 之CASCADE、SET NULL、RESTRICT、空等类型分析和作用解读
空、RESTRICT、NO ACTION
删除:从表记录不存在时,主表才可以删除。删除从表,主表不变
更新:从表记录不存在时,主表才可以更新。更新从表,主表不变
CASCADE
删除:删除主表时自动删除从表。删除从表,主表不变
更新:更新主表时自动更新从表。更新从表,主表不变
SET NULL
删除:删除主表时自动更新从表值为NULL。删除从表,主表不变
更新:更新主表时自动更新从表值为NULL。更新从表,主表不变
MySql不同数据库之间表批量移动
SET GLOBAL group_concat_max_len=1024000000;
SELECT CONCAT('RENAME TABLE ', GROUP_CONCAT('`default`.`', `TABLE_NAME`, '` TO `spilt_0`.`', `TABLE_NAME`, '`')) FROM INFORMATION_SCHEMA.`TABLES` WHERE `TABLE_SCHEMA`='default';