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%'
;
# or
SHOW 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 ***
