MySQL

用户管理

删除匿名账户: shell> mysql -u root mysql> DELETE FROM mysql.user WHERE User = ”; mysql> FLUSH PRIVILEGES;

创建新用户: mysql> grant all privileges on shangcheng.* to shangcheng@localhost identified by ‘123456’;

修改 root 密码: shell> mysqladmin -u root password “newpass” shell> mysqladmin -u root password oldpass “newpass”

杀连接

mysql> DROP TABLE `user_auth`;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> SHOW PROCESSLIST;
+--------+------+-----------------+------------------------+---------+------+-------+------------------+----------+
| Id     | User | Host            | db                     | Command | Time | State | Info             | Progress |
+--------+------+-----------------+------------------------+---------+------+-------+------------------+----------+
+--------+------+-----------------+------------------------+---------+------+-------+------------------+----------+
3 rows in set (0.00 sec)

mysql> kill 208611;

查看版本

mysql> SHOW VARIABLES LIKE “%version%”;

索引

组合索引,有范围查询的字段放在索引的后面。

可预测组合的范围查询修改成 in 查询。

单独创建一张单独的索引表。seti

limit 100000, 10 时,mysql 会取出前 100010, 丢掉前面返回 10 条,可能恶意查询。

分页,last_id 的限制,当元素顺序会变动时。

last id 怎么用索引?

GET_LOCK()

Get a named lock.

GET_LOCK(str,timeout)

Tries to obtain a lock with a name given by the string str , using a timeout of timeout seconds. A negative timeout value means infinite timeout. The lock is exclusive. While held by one session, other sessions cannot obtain a lock of the same name .

Returns 1 if the lock was obtained successfully, 0 if the attempt timed out (for example, because another client has previously locked the name), or NULL if an error occurred (such as running out of memory or the thread was killed with mysqladmin kill).

A lock obtained with GET_LOCK() is released explicitly by executing RELEASE_LOCK() or implicitly when your session terminates (either normally or abnormally). Lock release may also occur with another call to GET_LOCK():

事务

手动写了一个 tag_pipeline 的表,修改 tag 关注关系时,同时把 pipeline 写到另外一张表里。 结果到最后统计数据时,有好大好大的差异。

text

员外: 有一点需要注意的,之前这三个字段是 text 类型,允许的最大长度是 64KB,调整编码为 utf8mb4 之后对于已有的部分超长 comment 来说 64KB 已不够用,为了避免截断已有数据,我把字段改为了 mediumtext charset utf8mb4,副作用是会允许这些字段存储最大 16MB 的单个 comment,为了避免不必要的麻烦,希望应用能做长度检查,避免不必要的过长评论进入存储。