MySQL的一些tips总结_2


1.MySQL中的通配符和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模块时新建的数据库):

mysql_escape

2.MySQL中不等于的写法
!=, <> Not equal operator
参考链接:
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()之后的处理。

参考链接:
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
参考链接:
,

《“MySQL的一些tips总结_2”》 有 2 条评论

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注