字符拼接

SHOW CREATE TABLE sys_area;

CREATE TABLE `sys_area` (
  `id` varchar(50) NOT NULL,
  `name` varchar(255) NOT NULL,
  `parentId` int(11) DEFAULT NULL,
  `code` varchar(1000) DEFAULT NULL COMMENT ''用来快速查询'',
  `priority` int(11) DEFAULT NULL,
  `fee` float(10,2) DEFAULT ''0.00'',
  `valid` int(11) DEFAULT ''1'',
  `weight` int(11) DEFAULT ''10'' COMMENT ''排序的权值'',
  `level` int(11) DEFAULT NULL,
  `available` int(11) DEFAULT ''0'' COMMENT ''0: 不可用, 1: 可用'',
  `status` tinyint(4) DEFAULT ''0'',
  `createUser` varchar(50) DEFAULT NULL,
  `createDate` timestamp NULL DEFAULT NULL,
  `updateUser` varchar(50) DEFAULT NULL,
  `updateDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_code` (`code`(180)) USING BTREE,
  KEY `idx_level` (`level`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT

UPDATE sys_area SET code = code + ',' WHERE code NOT REGEXP'%,';

Response:

Error Code: 1062. Duplicate entry ‘0’ for key ‘idx_code’

正确语句：

UPDATE sys_area SET code = CONCAT(code, ',') WHERE code NOT REGEXP'%,';

使用mysql workbench 6.2.5.0导出5.6.16-log到5.6.23失败

Response：
10:06:31 Restoring game_dev (admin_config)
Running: mysql.exe –defaults-file=”c:\users\liang\appdata\local\temp\tmpkjgddd.cnf” –protocol=tcp –host=127.0.0.1 –user=root –port=3306 –default-character-set=utf8 –comments < “F:\database\dumps\Dump20150518\game_dev_admin_config.sql”
ERROR 1839 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_MODE = ON.

Operation failed with exitcode 1

解决方案：

MySQL实现Oracle中的 rownum 功能

示例代码如下：

SELECT @curRow := @curRow + 1 as rownum, tn.* FROM database_name.table_name tn INNER JOIN (SELECT @curRow := 0) temp;

删除数据库语句

创建临时数据库时，没注意规范，命名成了a.b的格式，结果在删除该数据库时，提示SQL语句出错，因为 . 在程序中一般用作隶属或者分隔命名空间的作用，同属于关键字，故数据库命名尽量不要含英文句点(.)，可以使用下划线代替，估计很少人遇到这个问题。不过，即便遇到这个问题，也有办法解决，很简单，将数据库名称用反引号(`)引起来就行了。
最后执行的代码如下：

DROP DATABASE `database_name`;

存储过程示例1

该存储过程主要用于添加测试数据。
展示如何创建及调用存储过程，以及在存储过程中使用游标。

DROP PROCEDURE IF EXISTS init_match_menu_data;
delimiter //
CREATE PROCEDURE init_match_menu_data ()
BEGIN
    # 声明变量
    DECLARE match_id VARCHAR(50);
    DECLARE match_icon VARCHAR(50);
    DECLARE match_name VARCHAR(50);
    DECLARE i INT DEFAULT 0;
    DECLARE done TINYINT(1) DEFAULT 0;
    # 声明游标
    DECLARE matchList CURSOR FOR SELECT id, icon, name FROM `match` WHERE status >= 0 LIMIT 1000;
    # 将结束标志绑定到游标
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    # 打开游标
    OPEN matchList;
    READ_LOOP: LOOP 
        # 提取游标里的数据
        FETCH matchList INTO match_id, match_icon, match_name;
        # 声明结束的时候
        IF done THEN
            LEAVE READ_LOOP;
        END IF;
        # 做爱做的事
        SET i = i + 1;
        INSERT INTO bm_match_menu(id, matchId, title, icon, content, content1, seq, status)
        VALUES (i, match_id, '赛事规程', match_icon, CONCAT(match_name, '0'), CONCAT(match_name, '0'), 0, 0);

        SET i = i + 1;
        INSERT INTO bm_match_menu(id, matchId, title, icon, content, content1, seq, status)
        VALUES (i, match_id, '赛事规程', match_icon, CONCAT(match_name, '1'), CONCAT(match_name, '1'), 0, 1);

        # select match_id, match_icon, match_name, i;    # 不能使用select @match_id
    END LOOP;    # 循环结束
    CLOSE matchList;    # 关闭游标
END //
delimiter ;

调用存储过程

CALL init_match_menu_data();

存储过程示例2

该存储过程是为了查找指定数据库指定表中，字符集不正确的列，并将其更新为指定字符集编码。
主要展示如何在存储过程中预编译并执行动态 SQL。

DROP PROCEDURE IF EXISTS update_column_collation_name;
DELIMITER $$
CREATE PROCEDURE `update_column_collation_name`(in in_schema_name varchar(50), in in_tbl_name varchar(50), in in_default_character_set varchar(50), in in_default_collation varchar(50))
BEGIN
  DECLARE tbl_name VARCHAR(50) DEFAULT NULL;
  DECLARE col_name VARCHAR(50) DEFAULT NULL;
  DECLARE col_type VARCHAR(50) DEFAULT NULL;
  DECLARE col_comment VARCHAR(50) DEFAULT NULL;
  DECLARE is_nullable TINYINT DEFAULT 0;
  DECLARE sql_string VARCHAR(1000) DEFAULT NULL;
  DECLARE done TINYINT DEFAULT 0;
  DECLARE column_list CURSOR FOR SELECT table_name, column_name, column_type, column_comment, IF(IS_NULLABLE = 'NO', 0, 1) is_nullable FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = in_schema_name and TABLE_NAME = in_tbl_name and ((CHARACTER_SET_NAME is not null and CHARACTER_SET_NAME <> in_default_character_set) or (COLLATION_NAME is not null AND COLLATION_NAME <> in_default_collation));
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN column_list;
  SET foreign_key_checks = 0;  # 因为修改字符集时，可能影响到外键约束，故先关闭外键检查
  READ_LOOP:
  LOOP
    FETCH column_list INTO tbl_name, col_name, col_type, col_comment, is_nullable;
    IF done THEN
      LEAVE READ_LOOP;
    END IF;
    SET sql_string = CONCAT('alter table `', in_schema_name, '`.`', tbl_name, '` change `', col_name, '` `', col_name, '` ', col_type, ' character set ', in_default_character_set,' collate ', in_default_collation, ' ', IF(is_nullable = 0, 'NOT NULL', 'NULL'), ' comment \'', col_comment, '\'');
    select sql_string; # 生成的动态sql
    SET @sql_string = sql_string; # PREPARE语句不能使用局部变量，所以在此处声明一个用户变量
    PREPARE stmt FROM @sql_string; # 预编译该SQL字符串
    EXECUTE stmt; # 执行
  END LOOP;
  SET foreign_key_checks = 1; # 恢复外键检查
END$$
DELIMITER ;

调用存储过程：

call update_column_collation_name('schema_name', 'table_name', 'utf8mb4', 'utf8mb4_general_ci');

存储过程示例3

该存储过程用于初始化表中的测试数据到指定数量。
主要展示了，如何给存储过程传递参数，以及 WHILE 语句的使用。

DROP PROCEDURE IF EXISTS init_test_data_screen_log;
delimiter //
CREATE PROCEDURE init_test_data_screen_log (IN max INT) 
BEGIN
  DECLARE i INT DEFAULT 20;
  SELECT MAX(id + 0) INTO i FROM screen_log;
  WHILE i < max DO
    SET i = i + 1;
    INSERT INTO screen_log(id, deviceId, type, code, message, status, createDate) 
    VALUES (i, 1, '设备', 'DEVICE_INIT', CONCAT(i, ' - 设备请求初始化:Agent-232,ip:192.168.3.20'), 0, NOW());
  END WHILE;
END //
delimiter ;

调用存储过程：

CALL init_test_data_screen_log(1000); # 初始化表中的测试数据到1000条

存储过程示例4

演示如何在存储过程中，预编译带参数的动态 SQL，以及向存储过程中传递参数。

DROP PROCEDURE IF EXISTS tmp_init_data; # 删除已经存在的存储过程
delimiter //
CREATE PROCEDURE tmp_init_data(IN prefix VARCHAR(10), IN init INT, IN max INT)  # 参数前面加 `IN` 表示，这个参数是入参，即由外界传递给此存储过程的，若为 `OUT` 这表示该参数将会返回给外界，此外还有 `INOUT`
BEGIN
  DECLARE _name VARCHAR(10) DEFAULT 'name';
  DECLARE i INT DEFAULT init;
  DECLARE sql_stmt VARCHAR(1000);
  SET @sql_insert = 'insert into test_repl.test_repl_table0(`name`) values(?);';

  WHILE i < max DO
    SET @name_ = CONCAT(prefix, i); # 声明一个用户变量，以便传递给预编译的 SQL
    PREPARE stmt FROM @sql_insert;
    EXECUTE stmt USING @name_; # `using` 后跟用户变量(`user variables`)
    SET i = i + 1;
  END WHILE;
END //
delimiter ;

时间格式化

工作中经常需要将时间格式化后输出，可以使用DATE_FORMAT这个函数：

mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
        -> 'Sunday October 2009'
mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
        -> '22:23:00'
mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
    ->                 '%D %y %a %d %m %b %j');
        -> '4th 00 Thu 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
    ->                 '%H %k %I %r %T %S %w');
        -> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
        -> '1998 52'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
        -> '00'

详细介绍可参考MySQL官网文档：12.7 Date and Time Functions

DELETE语法

单表语法：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [PARTITION (partition_name,...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

多表语法：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

或者

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

Warning: Using a password on the command line interface can be insecure.

在命令行上使用下面的方式登录MySQL时，会报此警告信息：

shell> mysql -u francis -pfrank db_name

警告内容翻译过来大概是：在命令行界面上使用密码可以是不安全的。
官方给出的解释：

This is convenient but insecure. On some systems, your password becomes visible to system status programs such as ps that may be invoked by other users to display command lines. MySQL clients typically overwrite the command-line password argument with zeros during their initialization sequence. However, there is still a brief interval during which the value is visible. Also, on some systems this overwriting strategy is ineffective and the password remains visible to ps. (SystemV Unix systems and perhaps others are subject to this problem.)

If your operating environment is set up to display your current command in the title bar of your terminal window, the password remains visible as long as the command is running, even if the command has scrolled out of view in the window content area.

简单来说，MySQL不推荐在命令行上直接使用密码。因为直接显示在命令行上的密码，可能被系统内的其他应用程序捕捉到，比如查看进程的ps命令，以及history命令等，都有可能造成密码泄露。
MySQL官方提供的解决方案有3种：
* 在命令行上使用 不跟密码值的-p 或者 --password选项 *，例如：

shell> mysql -u francis -p db_name
Enter password: ********

* 将密码保存在配置文件里。举个例子，在Unix系统上，你可以在你的家目录的.my.cnf文件的[client]部分，罗列你的密码 *，例如：

[client]
password=your_pass

为了保证密码的安全，该文件不应该除你之外的任何人或应用程序获取到。为了达到这一点，设置该文件访问权限为400或者600，例如：

shell> chmod 600 .my.cnf

* 把你的密码保存在名为MYSQL_PWD的环境变量中。 * 详细请移步至Section 2.12, “Environment Variables”.
官方参考文档：MySQL :: MySQL 5.6 Reference Manual :: 6.1.2.1 End-User Guidelines for Password Security：

Warning: World-writable config file ‘/usr/local/mysql/my.cnf’ is ignored

大概意思是：全局可写的/usr/local/mysql/my.cnf配置文件被忽略。
MySQL担心这种文件被其他用户或应用程序恶意修改，所以忽略掉这个配置文件。因此，修改该文件的权限为，该用户可读，其他用户不可写即可，例如：

shell> chmod 644 /usr/local/mysql/my.cnf

主从架构模式下，复制失败（中断）

2016年08月12日记：
在配置主从数据库时，给从库配置了主库的 log-bin 的文件名及位置后，并没有立即开启复制，而是在主库上做了其他操作，主要是修改了一个账户的密码和权限，这个账户是给从库登录主库使用的。
处理完成后，开启从库的复制功能，发现并没有执行复制，执行 show slave status，发现 Last_SQL_Error 列有内容，是一个修改密码和权限的的操作，具体内容没有记下来。初步判断是从库在同步主库的修改密码或者权限时，没有找到用户的异常，因为我修改的那个账户，不在从库上，而是在主库上。
翻阅 MySQL 官网文档 发现，Last_SQL_Errno 和 Last_SQL_Error 代表的是最近一次导致 SQL 线程停止的错误编号和对应的错误信息，同一页的上方，可以看到有写到：

Note
When the slave SQL thread receives an error, it reports the error first, then stops the SQL thread. This means that there is a small window of time during which SHOW SLAVE STATUS shows a nonzero value for Last_SQL_Errno even though Slave_SQL_Running still displays Yes.

也就是说当从库的 SQL 线程接收到错误信息时，它会首先报告这个错误，然后停止 SQL 线程。这也就意味着，在报告时并没有停止 SQL 线程，它们之间存在一个很小的窗口期，再具体一点就是，在你执行 SHOW SLAVE STATUS 时，看到的 Last_SQL_Errno 列的值不是 0，但 Slave_SQL_Running 列显示的依旧是 Yes。
到目前基本找到原因，是这个错误导致！原来是这个错误导致了从库停止了同步操作。一把 Google 后得知，可以执行 set global sql_slave_skip_counter = N; （N是一个整数）语句，跳过从主库同步过来的 N 条语句，保险起见，我执行了 set global sql_slave_skip_counter = 1; ，完美解决！
后来发现官网说这个全局变量有 Bug。还好没被我碰上，以后碰上再说…
说了这一堆，最终的解决方案是在发生问题的从库上执行以下命令：

stop slave;
set global sql_slave_skip_counter = 1;
start slave;

之后，使用 show slave status 查看问题是否已解决，若还有其他类似问题，可以多执行几次上面的命令。

Windows下非安装版MySQL的启动与停止

切换到 MySQL bin 目录下

cd /d d:\ProgramFiles\mysql-5.6.29-winx64\bin

• 启动
  启动端口为 3306 的实例

  mysqld

• 停止
  停止端口为 3306 的实例：

  mysqladmin -u root shutdown

  停止端口为 3307 的实例：

  mysqladmin -u root -P 3307 shutdown

主从复制时，只同步指定的数据库

若只想复制指定的一个或者多个库，可以在命令行或者配置文件中使用 --replicate-do-db 参数，该配置项是从库的配置选项。如果要指定多个数据库，可以使用多次该参数，比如，可以在从库的 my.cnf 中配置：

[mysqld]
# 忽略其他配置
replicate-do-db=db_name0
replicate-do-db=db_name1
replicate-do-db=db_name2

与之类似的还有 --replicate-ignore-db ，不过它与 --replicate-do-db 相反，--replicate-do-db 是用来指定哪些数据库需要复制，而 --replicate-ignore-db 用来指定哪些数据库不需要复制，两者可以配合使用。
参考官方链接：MySQL :: MySQL 5.6 Reference Manual :: 17.1.4.3 Replication Slave Options and Variables

终止指定的 SQL 线程

今天用存储过程向数据库添加一些测试数据，结果在 while 语句块：

  SET @sql_insert = 'insert into test_repl.test_repl_table0(`name`) values(?);';
  WHILE i < max DO
    SET @name_ = CONCAT(prefix, i);
    PREPARE stmt FROM @sql_insert;
    EXECUTE stmt USING @name_;
  END WHILE;

忘记进行 SET i = i + 1; 了… 结果可想而知，表中的数据失控性的增长，使用 SHOW PROCESSLIST 查看正在运行的线程，结果如下图：

可以看出 Id 列为 7 的列，就是正在无限执行的语句。

按照以往的做法，也是终极大法：重启 MySQL 服务…毕竟重启能解决 99% 的问题…不过一直感觉这个方法有点水，想换个专业点的…
Google了一把，很快找到了解决方案，使用 MySQL KILL 这个命令，杀死指定的线程。简单说下 KILL 这个命令。
每个与 mysqld 建立的连接，都运行在一个独立的线程里运行，可以使用 KILL 终止指定的线程。
KILL 不仅可以终止线程，还可以终止正在执行的语句。在终止之前，可以使用 SHOW PROCESSLIST 查看已建立连接或正在执行语句的线程 ID ，然后执行 KILL YOUR_PROCESS_ID 即可终止指定线程。
因此可以执行以下语句终止执行 SQL 语句：

KILL 7;

官方参考文档：MySQL :: MySQL 5.6 Reference Manual :: 13.7.6.4 KILL Syntax

MySQL Cursor Fetch无法取出值的问题

CentOS 7，MySQL 5.6

问题代码如下，article_id 为局部变量，ARTICLE.ARTICLE_ID 为主键列，在循环中 Fetch 游标中的值时，无法获取数据，SELECT article_id; 结果为空，独立执行定义CURSOR 的SQL语句是有结果的。一说是由 ARTICLE.ARTICLE_ID 为主键的缘故，非主键列未发现此问题。其实不然，经测试，非主键列同样有此问题。
问题的根本原因是 MySQL对 article_id 和 ARTICLE_ID 未区分大小写。十年前就被提出来了…详见 MySQL Bugs: #5967: Stored procedure declared variable used instead of column

DROP PROCEDURE IF EXISTS test;
delimiter $$
CREATE PROCEDURE test()
BEGIN
  DECLARE article_id VARCHAR(50) DEFAULT NULL;
  DECLARE done TINYINT(1) DEFAULT 0;
  DECLARE article_list CURSOR FOR SELECT ARTICLE_ID FROM ARTICLE LIMIT 2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN article_list;
  READ_LOOP:
  LOOP
    FETCH article_list INTO _article_id;
    IF done THEN
      LEAVE READ_LOOP;
    END IF;
    SELECT article_id;
  END LOOP;
  CLOSE article_list;
END$$
delimiter ;

创建支持Emoji表情的数据库

随手记

CREATE DATABASE IF NOT EXISTS `mydb` /*!40100 DEFAULT CHARACTER SET utf8mb4 DEFAULT  COLLATE = 'utf8mb4_unicode_ci' */;