MySQL日常笔记

2015-12-07

旧的MySQL函数与MySQLi和PDO映射关系表

MySQL Extension MySQLi PDO
mysql_affected_rows mysqli::$affected_rows PDOStatement::rowCount
mysql_client_encoding mysqli::character_set_name  
mysql_close mysqli::close Assign NULL to PDO Object
mysql_connect mysqli::__construct PDO::__construct
mysql_create_db Query: CREATE DATABASE  
mysql_data_seek mysqli_stmt::data_seek PDO::FETCH_ORI_ABS (?)
mysql_db_name Query: SELECT DATABASE()  
mysql_db_query    
mysql_drop_db Query: DROP DATABASE  
mysql_errno mysqli::$errno PDO::errorCode
mysql_error mysqli::$error_list PDO::errorInfo
mysql_escape_string    
mysql_fetch_array mysqli_result::fetch_array PDOStatement::fetch
mysql_fetch_assoc mysqli_result::fetch_assoc PDOStatement::fetch
mysql_fetch_field mysqli_result::fetch_field PDOStatement::getColumnMeta
mysql_fetch_lengths mysqli_result::$lengths PDOStatement::getColumnMeta
mysql_fetch_object mysqli_result::fetch_object PDOStatement::fetch
mysql_fetch_row mysqli_result::fetch_row PDOStatement::fetch
mysql_field_flags mysqli_result::fetch_fields PDOStatement::getColumnMeta
mysql_field_len mysqli_result::fetch_field_direct PDOStatement::getColumnMeta
mysql_field_name mysqli_result::fetch_field_direct PDOStatement::getColumnMeta
mysql_field_seek mysqli_result::field_seek PDOStatement::fetch
mysql_field_table mysqli_result::fetch_field_direct PDOStatement::getColumnMeta
mysql_field_type mysqli_result::fetch_field_direct PDOStatement::getColumnMeta
mysql_free_result mysqli_result::free PDOStatement::closeCursor
mysql_get_client_info mysqli::get_client_info PDO::getAttribute
mysql_get_host_info mysqli::$host_info PDO::getAttribute
mysql_get_proto_info mysqli::$protocol_version  
mysql_get_server_info mysqli::$server_info PDO::getAttribute
mysql_info mysqli::$info  
mysql_insert_id mysqli::$insert_id PDO::lastInsertId
mysql_list_dbs Query: SHOW DATABASES  
mysql_list_fields Query: SHOW COLUMNS  
mysql_list_processes mysqli::$thread_id  
mysql_list_tables Query: SHOW TABLES  
mysql_num_fields mysqli::$field_count PDOStatement::columnCount
mysql_num_rows mysqli_stmt::$num_rows PDOStatement::rowCount
mysql_pconnect mysqli::__construct PDO::__construct
mysql_ping mysqli::ping  
mysql_query mysqli::query PDO::query
mysql_real_escape_string mysqli::real_escape_string PDO::quote
mysql_result Combination PDOStatement::fetchColumn
mysql_select_db mysqli::send_query PDO::__construct
mysql_set_charset mysqli::character_set_name PDO::__construct
mysql_stat mysqli::stat PDO::getAttribute
mysql_tablename Query: SHOW TABLES  
mysql_thread_id mysqli::$thread_id  
mysql_unbuffered_query See Buffering Concepts  

linux全新安装MySQL的步骤

注意

以下说明假定您的系统上尚未安装任何MySQL版本（无论是由Oracle还是其他方分发）; 如果不是这种情况，请按照使用MySQL APT存储库替换MySQL的本机分发或 更换由Direct deb软件包下载的MySQL服务器中给出的说明进行操作 。

添加MySQL APT存储库

首先，将MySQL APT存储库添加到系统的软件存储库列表中。跟着这些步骤：

访问 https://dev.mysql.com/downloads/repo/apt/ 上的MySQL APT存储库的下载页面 。

选择并下载适用于Linux发行版的发行包。

使用以下命令安装下载的发行包，替换 version-specific-package-name 为下载的包的名称（如果未在包所在的文件夹中运行命令，则以其路径开头）：

shell> sudo dpkg -i /PATH/version-specific-package-name.deb

例如，对于w.x.y-z包的版本 ，命令是：

shell> sudo dpkg -i mysql-apt-config_w.x.y-z_all.deb

请注意，相同的包适用于所有受支持的Debian和Ubuntu平台。

在安装软件包期间，系统会要求您选择要安装的MySQL服务器版本和其他组件（例如，MySQL Workbench）。如果您不确定选择哪个版本，请不要更改为您选择的默认选项。 如果您不想安装特定组件，也可以选择none。在为所有组件做出选择后，选择“ 确定”以完成发布包的配置和安装。

您可以随时更改版本的选择; 有关说明，请参阅选择主要版本。

使用以下命令从MySQL APT存储库更新包信息（此步骤是必需的）：

shell> sudo apt-get update

您也可以手动添加和配置MySQL APT存储库，而不是使用发行包。 有关详细信息，请参阅 附录A：手动添加和配置MySQL APT存储库。

注意 在系统上启用MySQL APT存储库后，您将无法再从平台的本机软件存储库安装任何MySQL软件包，直到禁用MySQL APT存储库。

使用APT安装MySQL

通过以下命令安装MySQL：

shell> sudo apt-get install mysql-server

这将安装MySQL服务器的包，以及客户端和数据库公共文件的包。

在安装过程中，系统会要求您为root用户提供MySQL安装的密码。

重要

确保记住您设置的root密码。想要稍后设置密码的用户可以在对话框中将 密码字段留空，只需按确定即可 ; 在这种情况下，对于使用Unix套接字文件的连接，Socket Peer-Credential Pluggable Authentication将对服务器的root访问进行身份 验证。您可以稍后使用程序mysql_secure_installation设置root密码 。

启动和停止MySQL服务器

MySQL服务器在安装后自动启动。您可以使用以下命令检查MySQL服务器的状态：

shell> sudo service mysql status

使用以下命令停止MySQL服务器：

shell> sudo service mysql stop

要重新启动MySQL服务器，请使用以下命令：

shell> sudo service mysql start

注意

一些依赖于本机MySQL包的第三方本机存储库包可能无法与MySQL APT存储库包一起使用，因此不应与它们一起使用; 这些包括akonadi-backend-mysql，handlersocket-mysql-5.5和zoneminder。

选择其他版本

默认情况下，MySQL服务器和其他所需组件的所有安装和升级都来自您在安装配置包期间选择的主要版本的发行版系列（请参阅添加MySQL APT存储库）。但是，您可以通过重新配置已安装的配置包随时切换到另一个受支持的主要版本系列。使用以下命令：

shell> sudo dpkg-reconfigure mysql-apt-config

然后会出现一个对话框，要求您选择所需的主要版本。做出选择并选择确定。返回到命令提示符后，使用以下命令从MySQL APT存储库更新包信息：

shell> sudo apt-get update

下次使用apt-get install命令时，将安装所选系列中的最新版本。

您可以使用相同的方法更改要使用MySQL APT存储库安装的任何其他MySQL组件的版本。

两张结构相同的表联合查询，返回至同一个结构中

SELECT * FROM hack_site1 WHERE name = 'query'
UNION ALL
SELECT * FROM hack_site2 WHERE name = 'query'

优化

查询慢查日志是否开启，以及日志路径：

mysql> show variables like 'slow%';
+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| slow_launch_time    | 2                                |
| slow_query_log      | OFF                              |
| slow_query_log_file | /app/mysql/c836212589df-slow.log |
+---------------------+----------------------------------+
3 rows in set

查询慢查日志超时时间：

show variables like 'long_query_time';

开启慢查日志：

set global slow_query_log=on;

开启没使用索引的查询日志：

set global log_queries_not_using_indexes=on;

导入导出

把表t1里的100w-1000w之间的数据导出：

导出

select * from t1 limit 1000000,9000000 into outfile "d:/t1_1000000-9000000.txt";

在服务器上导入该文件进表t1:

导入

load data infile "/app/sql_tmp/t1_1000000-9000000.txt" into table t1;

远程导入本地的数据 (需要先开启远程导入)：

set global local_infile=on;  
load data local infile  "d:/t1_1000000-9000000.txt" into table t1;

查看

MySQL中看看表是如何创建的：

show create table user;
show create table user\G;

查看默认储存引擎：

show engines;

或

show variables like '%storage_engine%';

查看一个数据库的所有表：

select table_name from information_schema.tables where table_schema='dbname' and table_type='base table';

查看一张表的所有字段信息：

desc tablename

查看数据库状态(包含注释)：

SHOW TABLE STATUS FROM 'dbname';

查看数据库版本

select database(), version();

其他操作

存在则更新，不存在则插入 （表必须有主键或者是唯一索引）

replace into t_test set ikey=1,value='a';

mysql生成当前时间戳

unix_timestamp(now())

shell中执行mysql命令

mysql -uroot -e "show slave status \G"   #加-e参数

Windows Mysql修改默认储存引擎，my.ini添加以下行：

[mysqld]
default-storage-engine=MYISAM
default-tmp-storage-engine=MYISAM

MYSQL允许远程连接，监听所有IP

[mysqld]
bind-address="0.0.0.0"

查看以及设置sql_mode

SQL_MODE的设置其实是比较冒险的一种设置，因为在这种设置下可以允许一些非法操作，比如可以将NULL插入NOT NULL的字段中，也可以插入一些非法日期，如“2012-12-32”。因此在生产环境中强烈建议开发人员将这个值设为严格模式，这样有些问题可以在数据库的设计和开发阶段就能发现，而如果在生产环境下运行数据库后发现这类问题，那么修改的代价将变得十分巨大。此外，正确地设置SQL_MODE还可以做一些约束(Constraint)检查的工作。

SELECT @@sql_mode;
SELECT @@global.sql_mode;
SELECT @@session.sql_mode;

如果只想在会话中受到影响，请使用“@@session”，例如：

SET @@session.sql_mode ="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

如果想影响所有客户端，请使用“@@global”，例如：

SET @@global.sql_mode ="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

提高mysql备份效率，建议用mysql自带的mysqldump导出，它会把条目批量写入一条语句，而navicat导出是一条一条的语句（巨型数据量时，生成的文件会非常大）。

windows mysql导出数据库，进目录 wamp\bin\mysql\mysql5.6.17\bin\ 执行：

mysqldump.exe -u root -p user --default-character-set=utf8 > user.sql

其中user为数据库名

接下来可以用替换的方法把，把里面ENGINE=InnoDB改为ENGINE=MyISAM

导入：

mysql -u root -p aaa --default-character-set=utf8 < user.sql

innodb转myisam慢：

在超大数据量的时候（亿级数据量）innodb如果有索引，那么在转换的时候会很慢，建议先把索引删除再去转换。

use database_name
alter table xxx engine=myisam;

索引

myisam建立索引慢

数据量大的时候，建立索引会非常慢，可以通过以下办法去快速建立索引

1、进入mysql界面。use dbname;

2、导出相应表的数据。 select * from tab into outfile 'tab.txt'; 此处tab.txt文件在mysql的data目录里

3、删除相应表的数据，并置第一条记录为0。 truncate table tab;

4、创建索引。create index IDX_NAME using BTREE on tab (col);

   或：alter table tab add index name1 (name);  括号里name是表名，name1是索引名可以不写

   索引的方式有：BTREE、RTREE、HASH、FULLTEXT、SPATIAL

5、导入文件到相应表。load data infile 'tab.txt' into table tab;

优化索引大小

建立前缀索引

语法为：

ALTER TABLE table_name ADD KEY(column_name(prefix_length));

比如添加键值：

alter table test add key (city(7));

另一种前缀索引

ALTER TABLE `hack_site11` ADD INDEX `name` (`name`(7)) USING BTREE ,

命令行

登录：

mysql -uroot -h db.server.io -P 3306 -D temp_db -p
输入root用户的mysql密码

1、显示数据库列表：
show databases;
2、显示库中的数据表： 
use mysql;
show tables;
3、显示数据表的结构： 
describe 表名; 
4、建库： 
create database 库名; 
5、建表： 
use 库名； 
create table 表名 (字段设定列表)； 
6、删库和删表: 
drop database 库名; 
drop table 表名； 
7、将表中记录清空： 
delete from 表名; 
8、显示表中的记录： 
select * from 表名

锁库

全局锁库(只读)：

FLUSH TABLES WITH READ LOCK;

表级别的锁定:

lock tables test read;

解锁：

UNLOCK tables;

注：在退出mysql终端的时候都会隐式的执行unlock tables。也就是如果要让表锁定生效就必须一直保持对话。

用户操作

错误解决 1251- Client does not support authentication protocol

开启mysql远程登录，允许所有IP登录

mysql -uroot -p
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'passwd' WITH GRANT OPTION;
mysql> ALTER USER 'root'@'%' IDENTIFIED BY 'passwd' PASSWORD EXPIRE NEVER;
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'passwd';
mysql> FLUSH PRIVILEGES;

查看MYSQL数据库所有用户

mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
+---------------------------------------+
| query                                 |
+---------------------------------------+
| User: 'debian-sys-maint'@'localhost'; |
| User: 'mysql.session'@'localhost';    |
| User: 'mysql.sys'@'localhost';        |
| User: 'admin'@'localhost';             |
+---------------------------------------+
4 rows in set (0.26 sec)

给IP开放访问

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'1.92.16.10' IDENTIFIED BY 'password' WITH GRANT OPTION;

给所有IP开放访问

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

给指定数据库添加用户

mysql> GRANT ALL PRIVILEGES ON dbname.* to malu@'%' identified by 'password' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

dbname:表示数据库名

删除用户

mysql> DROP USER username@hostname;

修改用户密码

方法一:

适用于管理员或者有全局权限的用户重设其它用户的密码，进入命令行模式

mysql -u root -p
mysql> use mysql;
mysql> UPDATE user SET password=PASSWORD("new password") WHERE user='username';
mysql> FLUSH PRIVILEGES;
mysql> quit;

方法二:

mysql -u root -p 
mysql> use mysql; 
mysql> SET PASSWORD FOR  username=PASSWORD('new password');
mysql> quit;

方法三:

mysqladmin -u root "old password" "new password"

注：new password请输入你想要设置的密码。

方法四：

重置mysql里root用户密码， 解决报错：Access denied for user ‘root’@’localhost’ (using password: YES)

# /etc/init.d/mysqld stop 
# mysqld_safe --user=mysql --skip-grant-tables --skip-networking & 
# mysql -u root mysql 
mysql> UPDATE mysql.user SET password=PASSWORD('newpassword') where USER='root'; 
mysql> FLUSH PRIVILEGES; 
mysql> quit 
# /etc/init.d/mysqld restart

MySQL 5.7以上版本，会出现 ERROR 1054 (42S22): Unknown column ‘password’ in ‘field list’，请使用以下语句修改：

mysql> update mysql.user set authentication_string=password('newpassword') where user='root'; 
mysql> FLUSH PRIVILEGES;

错误解决

运行mysql时，提示Table ‘performance_schema.session_variables’ doesn’t exist

解决的方法是：

第一步：在管理员命令中输入：

mysql_upgrade -u root -p --force

第二步：重新启动mysql的服务：

net stop mysql
net start mysql

再次运行mysql，就解决了。

注意，两步缺一不可。

解决报错： table is marked as crashed and should be repaired

意思是表被标记为损坏，应进行修复

找到mysql的安装目录的bin/myisamchk工具，在命令行中输入：

myisamchk -c -r ../data/tablename/xxx.MYI

然后重启mysql （记得要重启mysql！）

解决报错: [ERROR] Can’t open the mysql.plugin table. Please run mysql_upgrade to create it.

可以先初始化datadir目录，处理办法，shell下执行：

$ mysql_install_db --user=mysql --datadir=/app/mysql/

innodb引擎frm文件被删除成为”孤表”的解决办法

直接删除’db/table1’表时提示出错。仔细观察数据目录db下，没有发现table1.frm文件。看来只是InnoDB 内部数据字典中存在该表，在数据文件中是缺少.frm文件的，也就是常常所说的“孤表”。

解决办法:

1.在另外一个数据库db2中创建一个与’db/table1’结构一样的表，把db2目录下的该表的table1.frm拷贝到目录db下。

2.现在你就可以进入数据库db下，删除该表table1了，删除后可以重建。

解决报错 PDOException: SQLSTATE[HY000] [2000] mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication.

解决办法：

mysql> SHOW VARIABLES LIKE 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | ON    |   ####这里表明开启了旧密码选项，旧密码方式认证对于有些客户端是不支持的，所以我们关闭它。
+---------------+-------+
1 row in set

### 接下来需要关闭旧密码

SET SESSION old_passwords=FALSE;
SET old_passwords=OFF;
SET PASSWORD = PASSWORD('mypasswd');

执行完以上三步就可以用客户端连接了。

mysql 5.1升级到mariab 10.1.30后报错 Cannot load from mysql.proc. The table is probably corrupted 解决办法

根据mysql手册，每次升级MySQL时，都应执行mysql_upgrade：

E:\Enviroment\Xampp_5.6.33\mysql\bin>mysql_upgrade.exe -u root -p
Enter password:
MySQL upgrade detected
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
......
Phase 7/7: Running 'FLUSH PRIVILEGES'
OK

Ubuntu下mysql目录迁移导致无法启动问题解决

查看mysql错误日志会发现如下信息：

/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
[ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.

解决办法：

把/etc/apparmor.d/usr.sbin.mysqld文件里 /var/lib/mysql/改成对应迁移后的路径 /mnt/HD2/mysql_data

vim /etc/apparmor.d/usr.sbin.mysqld

#/var/lib/mysql/ r,
#/var/lib/mysql/** rwk,
/mnt/HD2/mysql_data/ r,
/mnt/HD2/mysql_data/** rwk,

然后重新加载apparmor：

/etc/init.d/apparmor reload

最后启动mysql：

service mysql start

注：AppArmor 是一个类似于selinux 的东东，主要的作用是设置某个可执行程序的访问控制权限，可以限制程序 读/写某个目录/文件，打开/读/写网络端口等等。

some SQL

-- APP注册用户量：

SELECT count(*)+6000 FROM `users` WHERE group_id=3 and is_import=2;

-- APP下载量：

SELECT COUNT(*) FROM `app_downloads`;

-- 复购量（大于1次有购买行为的用户数量）：

SELECT COUNT(*) FROM (SELECT user_id,COUNT(*) as buy_times FROM `orders` GROUP BY user_id HAVING count(*)>1) a;  

-- 有过购买行为的用户量：

SELECT COUNT(*) FROM (SELECT user_id,COUNT(*) as buy_times FROM `orders` GROUP BY user_id) a;

-- APP订单量：

SELECT COUNT(*) FROM `orders` WHERE import_type!=1;

-- APP成交总额（通过APP下单，已完成订单的成交总额）：

SELECT SUM(sum_price) FROM `orders` WHERE import_type!=1 AND `status`=8;

MyBatis动态SQL

https://mp.weixin.qq.com/s/3erF19M9tQ5L-ZqaaAmxOg

https://www.cnblogs.com/homejim/p/9909657.html

SQL 语法速成手册

https://juejin.im/post/6844903790571700231