Home » Code » mysql相关小贴士——不定期更新

mysql相关小贴士——不定期更新

获得一个数据库所有表的记录总数

select table_name,table_rows from information_schema.tables where TABLE_SCHEMA = '数据库名' order by table_rows desc;

数据是估算的可能不精确,但相差不大。

查看一个数据库的表信息

SHOW TABLE STATUS [FROM `dbname`] [LIKE 'pattern'];#LIKE是对表名进行筛选
show create database <database_name>;#查看建库语句

查看系统变量

#查看全局变量
show global variables like '%char%';#编码
#查看本地/当前会话变量
show local/session variables like 'autocommit';

修改账户密码

mysqladmin -uroot -p原密码 password 新密码

注:不需要登陆mysql执行。

创建数据库

CREATE DATABASE `database_name` DEFAULT CHARSET=utf8 COLLATE utf8_general_ci

对于charset的选择,一般用utf8,若要支持emoji表情,使用utf8mb4。对于collate,一般utf8_general_ci,另外还有utf8_unicode_ci,general速度比较快,unicode则是校对得比较准确。当然了,也可以用utf8mb4_general_ci/utf8mb4_unicode_ci,ci是case insensitive的缩写,意思是不区分大小写。cs(case sensitive)则区分。以上规则同样适用于建表。

修改字段

ALTER TABLE `table_name` MODIFY `column_name` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '字段说明';#MODIFY,不修改字段名称,每个字段信息必须至少都得包含数据类型(int)
ALTER TABLE `table_name` MODIFY `column_name1` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '字段说明1',MODIFY `column_name2` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '字段说明2';#多个字段,需要多个MODIFY
ALTER TABLE `table_name` CHANGE `column_name` `new_column_name` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '字段说明';#CHANGE,修改字段名称 
alter table `table_name` comment = '表的注释' #修改表的注释

添加字段

ALTER TABLE `table_name` ADD `column_name` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '字段说明' [AFTER `xxx_column_name`];

修改表名

ALTER TABLE `table_name` rename `new_table_name`;

索引相关

#显示表的索引
SHOW INDEX IN|FROM `table_name`;

#创建索引
ALTER TABLE `table_name` ADD INDEX|UNIQUE|PRIMARY KEY| `index_name` (column list);
CREATE [UNIQUE] INDEX `index_name` ON `table_name` (column_list);

#重建索引
REPAIR TABLE `table_name` QUICK;

#删除索引
DROP INDEX `index_name` ON `table_name`;
ALTER TABLE `table_name` DROP INDEX|UNIQUE|PRIMARY KEY `index_name`;

存储过程和自定义函数

SHOW PROCEDURE|FUNCTION STATUS [LIKE 'pattern'];#查看,LIKE是对名称进行筛选
DROP PROCEDURE|FUNCTION IF EXISTS procedure_name|function_name;#删除

存储过程和函数都是属于数据库所有。

开放mysql外网连接

为了开发方便,经常需要外网连接mysql。默认my.cnf启用了bind-address值为127.0.0.1,这样其他IP就都连不上了,会报“2003 – Can’t connect to MySQL server on ‘x.x.x.x’ (10038)”这个错误。关掉这个配置或者将值置为0.0.0.0。如果写了多行(一行一个),是以最后一个为准的,也就是无法支持多个IP。

但即使是这样,依然报“1130 – Host ‘x.x.x.x’ is not allowed to connect to this MySQL server”,原因是用户的权限问题。一般的做法是新建一个用户,并授权。

create user 'xiaomlove'@'%' identified by '账户密码';#创建用户
grant all privileges on *.* to 'xiaomlove'@'%';#授权
flush privileges;#刷新授权表

或者直接改已有用户的Host为%估计也是可以的。这样就可以使用新账户进行外网连接了。

如果使用root账号,默认也是只能本机连接,会报”host xxx… not allow to”,一样要执行上边语句开放root账户的外网连接权限。

delete子查询

有时候需要通过子查询来获得删除条件,比如:

DELETE FROM `table_name` WHERE id IN (SELECT id FROM `table_name` GROUP BY `type`);

以上语句是有问题的,会报”[Err] 1093 – You can’t specify target table `table_name` for update in FROM clause”。总结起来原因就是,DELETE后边的第一层子句不能有WHERE、GROUP BY之类的条件,嵌套一层即可。

DELETE FROM `table_name` WHERE id IN ( SELECT id FROM (SELECT id FROM `table_name` GROUP BY `type`) a);

改造后,where子句相当于是”SELECT id FROM a”,没有WHERE/GROUP BY这样的条件了。

复制记录

INSERT INTO `table_name_1` SELECT * FROM `table_name_2` WHERE id = 'XXX';#不同表或同表无键冲突
INSERT INTO `table_name_1` col_1, col_2 [col_3, ...] SELECT col_1, col_2 [col_3,...] FROM `table_name_2` WHERE id = 'XXX';#不同表或同表有键冲突
INSERT INTO `table_name_1` col_1, col_2 [col_3, ...] SELECT 115, col_2 [col_3,...] FROM `table_name_2` WHERE id = 'XXX';#特定字段(col_1)使用固定值(115)
CREATE `table_name_1` AS SELECT * FROM `table_name_2` WHERE id > 'xxx';#创建表时从已有表插入数据 

批量更新——不同条件对应不同的值

一般的UPDATE语句,都是同一条件使符合条件的记录更新到同一个值。如何让不同条件的记录更新为不同的值呢,比如ID为1的记录的name更新为“姓名1”,ID为2的记录的name更新为“姓名2”。这种情况下,可以使用CASE WHEN语句:

UPDATE `table_name` 
SET name = CASE WHEN `id` = '1' THEN 'name1' WHEN `id` = 2 THEN 'name2' END, 
SET age = CASE WHEN `id` = '1' THEN 'age1' WHEN `id` = 2 THEN 'age2' END 
WHERE id IN (1, 2) 
LIMIT 2

切记:对于更新和插入,批量操作一般都要远远快于单条操作。

连表更新&删除

 
update record_attribute_tag_relationships relationship 
inner join lmb_record record on relationship.record_id = record.id 
set relationship.album_id = record.album_id, relationship.album_uid = record.album_uid, relationship.create_time = record.publish_time;

delete record.* from lmb_record record inner join record_attribute_tag_relationships relationship on record.id = relationship.record_id 
where record.record_time >= 1473868800 and record.record_time < 1473955200 and record.content_type = 3 and relationship.attribute_id in (3361);

#以上语句仅能删除record表的数据,若要删除连表的多个表的数据,不能使用别名
delete lmb_record, record_attribute_tag_relationships from lmb_record 
left join record_attribute_tag_relationships on lmb_record.id = record_attribute_tag_relationships.record_id 
where lmb_record.uid = $uid and lmb_record.content_type = 3

字段注释乱码查看

有时候数据表的数据正常,字段的comment(注释)却乱码,一般是因为建表时候的编码选择不正确导致的。Mysql的默认编码使用的是latin1,最后一个是1、2、3中的1,执行set names latin1之后,再

show full columns from `table_name`

如果还不行,需要知道建时候到底使用了什么编码。

密码重置

关闭mysql: killall -TERM mysqld。

安全模式启动mysql: mysqld_safe –skip-grant-tables –skip-networking。

这时候会占用当前窗口,另开一个终端窗口,登陆:mysql -uroot。

修改密码:use mysql;, update user set password=password(“新密码”);。退出exit。

然后退出安全模式的mysql(第一句关闭mysql的语句一样可用),正常启动即可。

注:在mysql 5.7中,可能找不到mysqld_safe,可以参照以下步骤:

1. Stop mysql:
systemctl stop mysqld

2. Set the mySQL environment option 
systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"

3. Start mysql usig the options you just set
systemctl start mysqld

4. Login as root
mysql -u root

5. Update the root user password with these mysql commands
mysql> UPDATE mysql.user SET authentication_string = PASSWORD('MyNewPassword')
    -> WHERE User = 'root' AND Host = 'localhost';
mysql> FLUSH PRIVILEGES;
mysql> quit

6. Stop mysql
systemctl stop mysqld

7. Unset the mySQL envitroment option so it starts normally next time
systemctl unset-environment MYSQLD_OPTS

8. Start mysql normally:
systemctl start mysqld

Try to login using your new password:
7. mysql -u root -p

参见:http://stackoverflow.com/questions/33510184/change-mysql-root-password-on-centos7

值不同时使用更多查询条件

假如有小数数和分钟数两个条件,当分钟数为0时,只需要小时数满足即可,若分钟数大于0,分钟数也需要满足。

SELECT * FROM `table_name` WHERE hour = xxx AND CASE WHEN minute &gt; 0 THEN minute = xxx ELSE TRUE END;

数据绑定——where

$sql = "select * from `users` where `id` = ? and (`name` like ? or `email` like ?) limit 10 offset 0";
$users = DB::select($sql, ['12', '%c%', '%c%']);

插入大数据

在使用source语句进行大数据插入时(一条语句上万条记录)时,容易报错:ERROR 2006 (HY000): MySQL server has gone away。这时可修改max_allowed_packet的值,将之改大,比如16M。

查看mysql服务器状态

控制台连接服务器后,直接执行status命令即可。

启动mysql

首先设置启用用户,默认不允许使用root用户。可以在my.cnf中的[mysqld]部分中添加user=mysql来设置。若在service或init.d中均没有启动脚本,安装目录中support-files文件夹下的mysql.service即是启动脚本,将之复制到/etc/init.d/即可使用。

find_in_set不能匹配多个的解决方案

存储数据有多个时采用逗号分割,匹配时若只需要匹配一个,直接使用诸如find_in_set(‘weibo’, clients)即可。但是如果要匹配多个呢,如找出客户端包含weibo, qq的,find_in_set(‘weibo,qq’, clients)是没有这样的语法的。使用like肯定是不行,这时候可以使用正则。如: concat(‘,’, clients, ‘,’) regexp ‘,(weibo|qq),’。参考自这里

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Time limit is exhausted. Please reload CAPTCHA.