MySql基本操作

备份

导入、导出数据库

导出： mysqldump -uroot db1 > db1.sql (注db1为database名)

导入：mysql -uroot test < db1.sql (注test为database名，将db1中所有的表及数据导入到test数据库)

导入、导出表

导出：mysqldump -uroot db1 tb1 tb2>tables.sql(注db1为database名，tb1 tb2为要导出的表列表，中间用空格隔开)

导入：mysql -uroot test < tables.sql(将db1数据库中的tb1和tb2表导入到test数据库)

https://zhidao.baidu.com/question/918251843038464259.html

基本操作

修改字段

修改字段属性

ALTER TABLE tb_name MODIFY 字段名称 字段类型 [完整性约束条件]

将email字段 VARCHAR(50)修改成VARCHAR(200)

注意，修改时如果不带完整性约束条件，原有的约束条件将丢失，如果想保留修改时就得带上完整性约束条件

ALTER TABLE user10 MODIFY email VARCHAR(200) NOT NULL DEFAULT 'a@a.com';

将card移到test后面

ALTER TABLE user10 MODIFY card CHAR(10) AFTER test;

将test放到第一个，保留原完整性约束条件

ALTER TABLE user10 MODIFY test CHAR(32) NOT NULL DEFAULT '123' FIRST;

https://www.cnblogs.com/huangxm/p/5736807.html

Lock wait timeout exceeded解决办法

Java执行一个SQL查询未提交，遇到1205错误。

java.lang.Exception:

Error updating database.  Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction

解决办法：

执行mysql命令:show full processlist;

查看事务表: select * from information_schema.innodb_trx;

然后找出查询语句的系统id：kill掉被锁住的线程id

(没有看到正在执行的很慢SQL记录线程，再去查看innodb的事务表INNODB_TRX，看下里面是否有正在锁定的事务线程，看看ID是否在show full processlist里面的sleep线程中，如果是，就证明这个sleep的线程事务一直没有commit或者rollback而是卡住了，我们需要手动kill掉)

创建用户赋予权限

修改表

select host,user from user where user='root';
如果上面查询到有信息,但host为localhost或其他值，就需要根据实际需求来更新表信息

update user set host='%' where user='root';
如果想任何的IP地址都可以使用root用户来远程登陆（注意如果上面的查询语句，查询出来有多条，就需要做一些处理，比如多加一个条件如where user='root' and host='localhost'或先删除其他人记录只保存一条user='root’的记录）

修改权限

CREATE USER 'username'@'host' IDENTIFIED BY 'password';
grant all privileges on *.* to 'username'@'host' identified by 'password' with grant option; (host为 % 是通配)
flush privileges;

alter table table_name modify column col_name int auto_increment;

Error Code: 1175

MySql 执行 DELETE FROM Table 时，报

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

错误，这是因为 MySql 运行在 safe-updates模式下，该模式会导致非主键条件下无法执行update或者delete命令，执行命令如下命令

SET SQL_SAFE_UPDATES = 0;

Windows下username取到当前用户名问题解决

在Windows系统下，系统自带的 username 为当前win的用户，用spring mvc 配置数据源的时候不能用 username 属性，否则会出现MySQL连接的用户名取到当前用户

--推荐
jdbc.username=user

–禁用（Windows）
username=user
在Linux和Mac不会出现这个问题，应该是没有自带的 username 系统属性，兼顾团队其他成员，还是不要用的好。

Linux下查看mysql 线程执行内容

iotop   #查看io占用高的线程，也可以用top命令查看
pstack  tid    # tid为占用比较高的线程（不是16进制）

获取秒数

unix_timestamp() 获取秒数
unix_timestamp()*1000 获取毫秒数

执行效率和时间

在sql语句前加上 explain
例如 explain SELECT IFNULL( sum( CASE WHEN b.NAME = 'BTC' AND a.direct = 3 THEN a.amount ELSE 0 END ), 0 ) btc_in, IFNULL( sum( CASE WHEN b.NAME = 'ETH' AND a.direct = 3 THEN a.amount ELSE 0 END ), 0 ) eth_in, IFNULL( sum( CASE WHEN b.NAME = 'USDT' AND a.direct = 3 THEN a.amount ELSE 0 END ), 0 ) usdt_in, IFNULL( sum( CASE WHEN b.NAME = 'BTC' AND a.direct = 4 THEN a.amount ELSE 0 END ), 0 ) btc_out, IFNULL( sum( CASE WHEN b.NAME = 'ETH' AND a.direct = 4 THEN a.amount ELSE 0 END ), 0 ) eth_out, IFNULL( sum( CASE WHEN b.NAME = 'USDT' AND a.direct = 4 THEN a.amount ELSE 0 END ), 0 ) usdt_out, IFNULL( sum( CASE WHEN b.NAME = 'BTC' AND a.direct = 1 THEN a.amount ELSE 0 END ), 0 ) btc_trans, IFNULL( sum( CASE WHEN b.NAME = 'ETH' AND a.direct = 1 THEN a.amount ELSE 0 END ), 0 ) eth_trans, IFNULL( sum( CASE WHEN b.NAME = 'USDT' AND a.direct = 1 THEN a.amount ELSE 0 END ), 0 ) usdt_trans FROM fiat_flow a LEFT JOIN coin b ON a.coin_id = b.id WHERE a.create_time >= 1542211200000 AND b.NAME IN ( 'BTC', 'ETH', 'USDT' )

各属性含义
id：select查询的序列号
select_type：select查询的类型，主要是区别普通查询和联合查询、子查询之类的复杂查询。

• SIMPLE, 表示此查询不包含 UNION 查询或子查询
• PRIMARY, 表示此查询是最外层的查询
• UNION, 表示此查询是 UNION 的第二或随后的查询
• DEPENDENT UNION, UNION 中的第二个或后面的查询语句, 取决于外面的查询
• UNION RESULT, UNION 的结果
• SUBQUERY, 子查询中的第一个 SELECT
• DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果

table：输出的行所引用的表。
partitions：匹配的分区。
type：联合查询所使用的类型。
type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref。

• system,表中只有一条数据. 这个类型是特殊的 const 类型.
• const,针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可.
• eq_ref,此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高.
• ref,此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了 最左前缀 规则索引的查询.
• range,表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中.
• index,表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据.index 类型通常出现在: 所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据. 当是这种情况时, Extra 字段 会显示 Using index.
• ALL,表示全表扫描, 这个类型的查询是性能最差的查询之一. 通常来说, 我们的查询不应该出现 ALL 类型的查询, 因为这样的查询在数据量大的情况下, 对数据库的性能是巨大的灾难. 如一个查询是 ALL 类型查询, 那么一般来说可以对相应的字段添加索引来避免.

possible_keys：指出MySQL能使用哪个索引在该表中找到行。如果是空的，没有相关的索引。这时要提高性能，可通过检验 WHERE子句，看是否引用某些字段，或者检查字段不是适合索引。
key：显示MySQL实际决定使用的键。如果没有索引被选择，键是NULL。
key_len：显示MySQL决定使用的键长度。如果键是NULL，长度就是NULL。文档提示特别注意这个值可以得出一个多重主键里mysql实际使用了哪一部分。
ref：显示哪个字段或常数与key一起被使用。
rows：这个数表示mysql要遍历多少数据才能找到，在innodb上是不准确的。
filtered：表示此查询条件所过滤的数据的百分比。
Extra：如果是Only index，这意味着信息只用索引树中的信息检索出的，这比扫描整个表要快。
如果是where used，就是使用上了where限制。
如果是impossible where 表示用不着where，一般就是没查出来啥。

• Using filesort,表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.
• Using index,“覆盖索引扫描”, 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错.
• Using temporary,查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.
• Using where,这意味着mysql服务器将在存储引擎检索行后再进行过滤。许多where条件里涉及索引中的列，当它如果并且读取索引时，就能背存储引擎检验，因此不是所有带有where子句的查询都会显示using where.
• Using index condition,Index Condition Pushdown (ICP)是MySQL 5.6版本中的新特性,是一种在存储引擎层使用索引过滤数据的一种优化方式.
• Using join buffer(Block Nested Loop),
• Using join buffer (Batched Key Access),
• Using MRR,