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

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

Posted by: xiaomiao 2016/06/17 in Code, Mysql Leave a comment

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

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

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

查询——不同值对应不同返回字段

SELECT
	s_type,
	baby_status,
	count(*)
FROM
	(
		SELECT
			id,
			s_type,
			s_time,
			(
				CASE
				WHEN s_time >= 0
				AND s_time < 90 THEN '0-90' WHEN s_time >= 90
				AND s_time < 180 THEN '91-180' WHEN s_time >= 180
				AND s_time < 270 THEN '181-270' WHEN s_time >= 270
				AND s_time < 366 THEN
					'270-366'
				ELSE
					'其他'
				END
			) AS 'baby_status'
		FROM
			lmb_theme_content
		WHERE
			content_type = 6
		AND s_type = 3
	) a
GROUP BY
	baby_status

连表更新&删除

 
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),’。参考自这里。

Tagged with:

Leave a Reply

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

*

Time limit is exhausted. Please reload CAPTCHA.