1.MySQL中的通配符和escape转义

参考链接：

• http://stackoverflow.com/questions/3070411/mysql-wildcards-and
• http://www.mysqltutorial.org/mysql-like/
• MySql的like语句中的通配符：百分号、下划线和escape

参考信息：

MySql的like语句中的通配符：百分号、下划线和escape

% 代表任意多个字符

select * from user where username like '%zero';
select * from user where username like 'zero%';
select * from user where username like '%zero%';

_ 代表任意一个字符

select * from user where username like '_';
select * from user where username like 'zer_';
select * from user where username like '_ero';

如果我就真的要查%或者_，怎么办呢？使用escape，转义字符后面的%或_就不作为通配符了，注意前面没有转义字符的%和_仍然起通配符作用：

select username from user where username like '%zero/_%' escape '/';
select username from user where username like '%zero/%%' escape '/';

实际测试情况（之前学习Python的MySQLdb模块时新建的数据库）：

2.MySQL中不等于的写法

!=, <>

Not equal operator

参考链接：

• https://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html
• https://dev.mysql.com/doc/refman/5.1/en/non-typed-operators.html
• mysql 不等于 符号写法

3.MySQL中datetime类型变量的比较

参考链接：

mysql中datetime比较大小问题

4.编写Python脚本获取MySQL库、表信息

代码片段如下：

def get_records(cur, db, table, count=10):
    results = []
    cur.execute('select * from `%s`.`%s` limit %d;' % (db, table, count))
    results = [','.join(str(v) for v in row) for row in cur.fetchall()]
    if len(results) == 0:
        return []
    else:
        return results

易错点在于：cur.fetchall()之后的处理。

参考链接：

• http://stackoverflow.com/questions/10880813/typeerror-sequence-item-0-expected-string-int-found
• http://stackoverflow.com/questions/19641579/python-convert-tuple-to-string
• http://segmentfault.com/blog/qiwsir/1190000000715408

5.如何重置MySQL密码？

在 Linux 下：

如果 MySQL 正在运行，首先杀之：

$ sudo kill -9 mysqld_pid

然后以安全模式启动 MySQL ：

$ bin/mysqld_safe --skip-grant-tables &

另开一个 MySQL客户端 就可以不需要密码就进入 MySQL 了。并在另外一个 MySQL客户端 中执行命令：

mysql> update mysql.user set password=password("new_password") where user="root";
mysql> flush privileges;

更新了密码之后退出控制台，重启MySQL服务。

6.无法关闭mysqld_safe怎么办？

有时候会发现刚才启动的 mysqld_safe 无法关闭了：

mysqld_safe mysqld restarted

每kill掉一次就会自动restart，让人很恼火，找了一圈没找到，最后发现：

http://serverfault.com/questions/214922/mysql-wont-stop-mysqld-safe-appeared-in-top

Remember, mysqld_safe IS NOT MYSQL. It has a loop in it to call mysqld, the actual server daemon process. It was probably looping itself silly trying to start up mysqld. That was all the problem was.You should always:

1. make sure the socket file from the last time mysqld ran is gone.

2. check the mysql error log after a failed startup.

$ sudo mv /path/to/mysqld/mysqld.pid /path/to/mysqld/mysqld.pid.bak
$ sudo mv /path/to/mysql/mysql.sock /path/to/mysql/mysql.sock.bak
$ sudo kill -9 mysqld_pid

$ sudo service mysqld status
mysqld dead but pid file exists

$ sudo service mysqld status
mysqld is stopped

$ sudo service mysqld start
Starting mysqld: [ OK ]

7.怎样添加新用户？

直接grant命令添加用户&赋予相应权限：

mysql> CREATE DATABASE IF NOT EXISTS db_name;
mysql> GRANT ALL PRIVILEGES ON db_name.* TO 'db_user'@'localhost' identified by 'new_password';
mysql> flush privileges;

搜索关键字：

• MySQL show grants
• MySQL add new user and database
• mysqld_safe mysqld restarted

参考链接：

• https://dev.mysql.com/doc/refman/5.0/en/show-grants.html
• https://dev.mysql.com/doc/refman/5.5/en/create-database.html
• http://stackoverflow.com/questions/5016505/mysql-grant-all-privileges-on-database
• http://stackoverflow.com/questions/1720244/create-new-user-in-mysql-and-give-it-full-access-to-one-database
• https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql
• http://serverfault.com/questions/214922/mysql-wont-stop-mysqld-safe-appeared-in-top