分享程序员开发的那些事...
更新时间：2022-09-09 08:34:08
1.1、ignore
todo
1.2、replace into
todo
1.3、on duplicate key update
todo
|
1
2
|
INSERT INTO tbl (columnA,columnB,columnC) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE columnA=IF(columnB>0,1,columnA)
|
2、关于 MySQL 的超时值。
1）、查看 MySQL 的各种超时值。
|
1
2
3
|
SHOW GLOBAL VARIABLES WHERE VARIABLE_NAME LIKE '%timeout%';
# orSHOW GLOBAL VARIABLES LIKE '%timeout%';
|
4、用 sql 语句查看 MySQL/MariaDB 版本。
|
1
|
SELECT VERSION();
|
5、查看mysql当前连接数。
1）、查看状态：
|
1
|
SHOW STATUS; |
2）、查看当前连接数：
|
1
|
SHOW STATUS WHERE VARIABLE_NAME='Threads_connected';
|
3）、查看当前用户有哪些连接：
|
1
|
SHOW processlist; |
4）、查看所有用户连接。（需root权限）
|
1
|
SHOW full processlist;
|
6、修改mysql最大连接数。
1）、查看：
|
1
2
3
4
|
/* 查看所有全局变量 */SHOW GLOBAL VARIABLES;
/* 查看最大连接数 */SHOW GLOBAL VARIABLES WHERE VARIABLE_NAME='MAX_CONNECTIONS';
|
2）、临时修改：
|
1
|
SET GLOBAL max_connections=3600;
|
3）、写入配置文件，以Windows下MariaDB为例，在C:\Program Files\MariaDB 10.0\data\my.ini的
[MySQLd]段增加或修改 max_connections=3600 。
7、查看大表的行数。
对于上千万数量级大表，直接用
|
1
|
SELECT COUNT(*) FROM tablename;
|
即使建了主键或唯一索引也还是很慢。可以用下面两种方式查看。
（1）、看下面结果的rows列。
|
1
2
|
SHOW TABLE STATUS FROM mydbname
WHERE Name='mytablename';
|
（2）、看下面结果的TABLE_ROWS列。
|
1
2
3
|
SELECT * FROM information_schema.TABLES
WHERE TABLE_SCHEMA='mydbname' AND TABLE_NAME='mytablename'
; |
上面两种方式对于MyISAM存储引擎结果是准确的；对于InnoDB存储引擎，结果是近似值，可能还差很远。walker对InnoDB一次测试的状况是，实际数据条数为31182219，上面方式得出值为38569811。可参考文档SHOW TABLE STATUS Syntax关于rows的描述。
8、MySQL利用存储过程执行循环语句。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
USE dbname;DELIMITER $$ --定义结束符为$$
DROP PROCEDURE IF EXISTS test_insert; --删除已有存储过程
CREATE PROCEDURE test_insert() --创建新的存储过程
BEGIN DECLARE i INT DEFAULT 1;
WHILE i<10 DO
INSERT INTO tableName(idx) VALUES (i);
SET i=i+1;
END WHILE;
END $$ --结束定义语句
DELIMITER ; --恢复结束符为;
CALL test_insert(); --调用存储过程
|
9、将文本导入到mysql，load file示例。（行内分隔符为Tab，换行符为\n）
|
1
2
|
LOAD DATA [LOCAL] INFILE 'D:\\tmp\\test.txt' IGNORE INTO TABLE tableName FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';
|
10、从mysql导出数据到文本
（1）、select ...into outfile，只能导出到服务端。
|
1
2
|
SELECT fieldname FROM tableName
INTO OUTFILE 'D:\\-9.txt' LINES TERMINATED BY '\n';
|
（2）、mysql -e "SELECT ..." > file_name
|
1
|
mysql -h my.db.com -u usrname --password=pass db_name -e 'SELECT foo FROM bar' > /tmp/myfile.txt
|
注意：如果密码用短命令格式-ppass，-p后不能有空格。
11、用 mysqldump 迁移数据。
（1）、两台 mysql 服务器能直连。
|
1
|
mysqldump --databases db_name | mysql -uuser -p*** [--compress] -h 121.121.121.121 db_name |
（2）、两台 mysql 服务器不能直连。
|
1
2
3
4
5
|
#备份数据库mysqldump -uuser -p*** dbname > bakfile.sql#还原数据库mysql -uuser -p** dbname < bakfile.sql |
12、mysql将数字字符串转换为数字的方法。参考这里。
|
1
2
3
|
方法一：SELECT CAST('123' AS SIGNED);
方法二：SELECT CONVERT('123', SIGNED);
方法三：SELECT '123'+0;
|
13、关于数据库的字符集：
（1）、十分钟搞清字符集和字符编码
（2）、Aliyun RDS Change MySQL Charset From utf8 To utf8mb4
14、修复/优化表。
|
1
2
3
4
5
|
#修复表（仅MyISAM引擎支持repair） REPAIR TABLE `table_name`;#优化表 OPTIMIZE TABLE `table_name`; |
16、修改用户密码。（可参考这里）
|
1
2
3
|
use mysql; UPDATE user SET password=password('newpassword') WHERE user='root';
flush privileges;
|
相关阅读：
*** walker ***
