1、mysql 忽略主键冲突、避免重复插入的几种方式 。

1.1、ignore,主键重复则不插入新数据。

示例:

INSERT IGNORE INTO tbl (columnA,columnB,columnC) VALUES (1,2,3);

1.2、replace into,主键重复时用新数据整条替换老数据。

示例:

REPLACE INTO tbl (columnA,columnB,columnC) VALUES (1,2,3);

1.3、on duplicate key update,主键重复时更新部分字段。

示例:

INSERT INTO tbl (columnA,columnB,columnC) VALUES (1,2,3) 
ON DUPLICATE KEY UPDATE columnA=IF(columnB>0,1,columnA);

2、关于 MySQL 的超时值。

1)、查看 MySQL 的各种超时值。

SHOW GLOBAL VARIABLES WHERE VARIABLE_NAME LIKE '%timeout%';
# or
SHOW GLOBAL VARIABLES LIKE '%timeout%';

2)、设置MySQL的连接超时参数可以参考:

3、Windows下移动MariaDB数据目录

4、用 sql 语句查看 MySQL/MariaDB 版本。

SELECT VERSION();

5、查看mysql当前连接数。

1)、查看状态:

SHOW STATUS;

2)、查看当前连接数:

SHOW STATUS WHERE VARIABLE_NAME='Threads_connected';

3)、查看当前用户有哪些连接:

SHOW processlist;

4)、查看所有用户连接。(需root权限)

SHOW full processlist;

6、修改mysql最大连接数。

1)、查看:

/* 查看所有全局变量 */
SHOW GLOBAL VARIABLES;
/* 查看最大连接数 */
SHOW GLOBAL VARIABLES WHERE VARIABLE_NAME='MAX_CONNECTIONS';

2)、临时修改:

SET GLOBAL max_connections=3600;

3)、写入配置文件,以Windows下MariaDB为例,在C:\Program Files\MariaDB 10.0\data\my.ini

[MySQLd]段增加或修改 max_connections=3600 。

7、查看大表的行数。

  对于上千万数量级大表,直接用

SELECT COUNT(*) FROM tablename;

即使建了主键或唯一索引也还是很慢。可以用下面两种方式查看。

(1)、看下面结果的rows列。

SHOW TABLE STATUS FROM mydbname
WHERE Name='mytablename';

(2)、看下面结果的TABLE_ROWS列。

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利用存储过程执行循环语句。

USE dbname;

DELIMITER $$  --定义结束符为$$

DROP PROCEDURE IF EXISTS test_×××ert;  --删除已有存储过程 

CREATE PROCEDURE test_×××ert()  --创建新的存储过程
	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_×××ert();  --调用存储过程

9、将文本导入到mysql,load file示例。(行内分隔符为Tab,换行符为\n)

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,只能导出到服务端。

SELECT fieldname FROM tableName  
INTO OUTFILE 'D:\\-9.txt' LINES TERMINATED BY '\n';

(2)、mysql -e "SELECT ..." >  file_name

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 服务器能直连。

mysqldump --databases db_name | mysql -uuser -p*** [--compress] -h 121.121.121.121 db_name

(2)、两台 mysql 服务器不能直连。(mysqldump with create database line

#备份数据库,如果没有 -B 参数导出语句不会创建数据库
mysqldump -uuser -p*** [-B] dbname > bakfile.sql

#还原数据库
mysql -uuser -p*** dbname [--default-character-set=utf8] < bakfile.sql

(3)、导出指定表。

mysqldump -uuser -p*** -t dbName --tables tableName > bakfile.sql

12、mysql将数字字符串转换为数字的方法。参考这里

方法一: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、修复/优化表。

#修复表(仅MyISAM引擎支持repair) 
REPAIR TABLE `table_name`;

#优化表 
OPTIMIZE TABLE `table_name`;

15、MySQL整数(int)的取值范围

wKiom1d8di2BlbCyAABA6xQoJAg938.png

16、修改用户密码。(可参考这里

use mysql;  
UPDATE user SET password=password('newpassword') WHERE user='root';  
flush privileges;

17、修改外键数据。

17.1、全局更改

# 禁用外键约束
SET FOREIGN_KEY_CHECKS=0;
# 启用外键约束
SET FOREIGN_KEY_CHECKS=1;
# 查看外键约束
SELECT @@FOREIGN_KEY_CHECKS;

17.2、表级更改

18、找出 url 字段斜线(/)字符大于两个的条目。

SELECT * FROM issue  
WHERE (LENGTH(url) - LENGTH( REPLACE (url, '/', ''))) >2;

19、查看表的创建命令

SHOW CREATE TABLE my_table_name;


相关阅读


*** walker ***