mysql

show processlist;
show global status like 'Thread%';
show variables like '%connect%';


清理日志
show variables like '%log'
set global general_log = OFF
查看/var/lig/mysql/mysql/general_log文件，已经清空
set global general_log = ON

索引

查询表的主键和外部键
select constraint_name,column_name,referenced_table_name, referenced_column_name
from information_schema.key_column_usage
where table_schema = 'djrest' and table_name = 'snippet'

alter table caijixinxi add index cat_index(cat);
create index caiji_cat_index on caijixinxi(cat);
show index from caijixinxi;

drop index cat_index on caijixinxi;
alter table caijixinxi drop index cat_index;

删除主键约束：
alter table caijixinxi drop primary key;
alter table caijixinxi add primary key(id);
alter table caijixinxi add primary key(id, focus);

show index from auth_group; }}} mysql8连接caching_sha2_password问题

alter user 'root'/'localhost' identified with mysql_native_password by '123abc'
flush frivileges

中文乱码

改动mysql配置文件/etc/my.cnf。

[mysqld]
character-set-server=utf8mb4
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
collation-server=utf8mb4_general_ci

service mysqld restart

使用MySqli操作数据库

升级mysql

下载MySQL Yum Repository:
wget https://dev.mysql.com/get/mysql80-community-release-el7-2.noarch.rpm

安装yum库:
rpm -Uvh mysql80-community-release-e7-2.noarch.rpm

列表mysql库的激活状态:
yum repolist all | grep MySQL

安装yum-config-manager工具集:
yum -y install yum-utils

关闭mysql8.0, 激活mysql5.7库:
yum-config-manager --disable mysql80-community
yum-config-manager --enable mysql57-community

安装当前版本:
yum install mysql-community-server

运行mysql:
service mysqld start
systemctl start mysqld.service

MySQL 5.6升级至MySQL 5.7

删除ibdata1文件

vim /etc/my.cnf
innodb_file_per_table=1
rm /var/lib/mysql/ibdata1

清除ibdata1文件
删除ibdata1、ib_logfile0 ib_logfile1

慢查询

long_query_time=2
slow_query_log=1
slow_query_log_file=/var/lib/mysql/hkcd_av-slow.log

主键从0开始
mysql> SET sql_mode='NO_AUTO_VALUE_ON_ZERO';
mysql> insert app_config(id, name) values(0, 'ttt');

用户及权限

创建用户
create user dba@localhost identified by 'dbpass';
create user dba@'%' identified by 'dbpass';
insert mysql.user(host,user,password) values('localhost','dba', password('dbpass'));
flush privileges;

修改密码
set password for dba@localhost = password('dbpass');
set password = password('dbpass');      //修改当前用户密码

创建数据库及授权
create database testdb;
use testdb;
GRANT ALL PRIVILEGES ON *.* TO dba@172.128.0.1 IDENTIFIED BY 'passwd' GRANT OPTION;
grant all privileges on testdb.* to dba@localhost;

flush privileges;

grant create,alter,drop on dbtest.* to dba@localhost;
grant select,insert,update on dbtest.* to dba@'192,168.31.%';
grant select on dbtest.orders to dba@localhost;

取消授权
revoke all on *.* from dba@localhost
revoke insert on dbtest.* from dba@localhost

查看授权
show grants
show grants for dba
show grants for dba@locallhost;

mysql配置

grant all privileges on *.* to root@152.101.169.49 identified by 'msm25592265_ycm' with grant option;
flush privileges;

set global event_scheduler=ON
set global max_allowed_packet = 1024*1024*4
show global variables like '%general_log%';
set global general_log='ON'
set global log_output='TABLE';    可设置NONE,FILE,TABLE
set global general_log_file='/data/var/doc.log

set global slow_query_log='ON'
set global slow_query_log_file='/var/lib/mysql/slow.log'
set global long_query_time=2

#vim /etc/my.cnf
[mysqld]
max_allowed_packet = 4M
log = /data/server/mysql/20180305.log
slow_query_log=on
slow_query_log_file=/usr/local/mysql/slow.log
log_query_time=2

mysql>select @@max_allowed_packet;      初始化的全局参数
mysql>show global variables like '%max_allowed_packet%';    设置的全局参数

MySQL的BLOB类型操作

TinyBlob：  最大 255字节
Blob：      最大 65K
MediumBlob：最大 16M
LongBlob：  最大 4G

(1)操作大文本
<?php
  mysql_connect( "localhost", "root", "password"); //连接数据库
  mysql_select_db( "database"); //选定数据库
  //数据插入：
  $CONTENT="测试内容";  //$CONTENT为新闻内容
  $COMPRESS_CONTENT = bin2hex(gzcompress($CONTENT));
  $result=mysql_query( "insert into news (content) value ('$COMPRESS_CONTENT')");//数据插入到数据库news表中
  //展示：
  $query = "select data from testtable where filename=$filename";
  $result = mysql_query($query);
  $COMPRESS_CONTENT=@gzuncompress($result["COMPRESS_CONTENT"]);
  echo $COMPRESS_CONTENT;

(2)存储图片
<?php
mysql_connect( "localhost", "root", "password"); //连接数据库
mysql_select_db( "database"); //选定数据库
//存储：
$filename="" //这里填入图片路径
$COMPRESS_CONTENT = addslashes(fread(fopen($filename, "r"), filesize($filename)));//打开文件并规范化数据存入变量$data中
$result=mysql_query( "insert into news (content) value ('$COMPRESS_CONTENT')");//数据插入到数据库test表中
//展示：
ob_end_clean();
Header( "Content-type: image/gif");
$query = "select data from testtable where filename=$filename";
$result = mysql_query($query);
echo $result["COMPRESS_CONTENT"];

I try to insert a Open Office document on a blob field. To do this I try

INSERT INTO my_table (stamp, docFile) VALUES (NOW(), LOAD_FILE('/tmp/my_file.odt'));

UPDATE `TableName` SET `ColumnName`=LOAD_FILE('FilePath/FileName.bin') WHERE `YourCondition` 
// FilePath -> C:/foldername/filename.bin

2017-11-17

存贮过程、触发器与事件

创建procedure
DELIMITER //
create procedure proc01()
begin
    select id,title from caijixinxi limit 1;
end
//
DELIMITER ;

mysql_upgrade.exe -uroot -p --force

方法一：
       select `name` from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE'
 
方法二：
         show procedure status;
 
 查看存储过程或函数的创建代码
 
show create procedure proc_name;
show create function func_name;
----------------------------------
create trigger `num` BEFORE INSERT on `test1`
for each row 
    update test2 set num = num + 1;
----------------------------------
show global variables like 'event_scheduler';
set global event_scheduler=1

DROP EVENT e_newshits;
CREATE EVENT e_newshits   
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)   
ON COMPLETION PRESERVE ENABLE   
DO  
   DELETE FROM newshits;  

show create event e_newshits;

MySQL备份和恢复数据表的方法

mysql的配置文件my.ini:
basedir="D:/apps/temp/MySQL/MySQL Server 5.5/"
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
lower_case_table_names = 1
　　0：区分大小写
　　1：不区分大小写

只备份库里的一个表
mysqldump -uroot -proot mydb dept> dept.sql

恢复这个数据文件就和以前一样了，把只有一个表的数据导入到库里
mysql -u root -p mydb < dept.sql

还有一个很好玩，也记录下来了
mysql>select * from dept into outfile "dept.sql"
mysql>select * into outfile 'dept.sql' from dept;

mysql>tee a.sql;
mysql>select * from dept;
mysql>notee;


假定表tbl_name具有一个PRIMARY KEY或UNIQUE索引，备份一个数据表的过程如下：
1、锁定数据表，避免在备份过程中，表被更新
mysql>LOCK TABLES READ tbl_name;
2、导出数据
mysql>SELECT * INTO OUTFILE ‘tbl_name.bak’ FROM tbl_name;
3、解锁表
mysql>UNLOCK TABLES;

相应的恢复备份的数据的过程如下：
1、为表增加一个写锁定：
mysql>LOCK TABLES tbl_name WRITE;
2、恢复数据
mysql>LOAD DATA INFILE ‘tbl_name.bak’
  ->REPLACE INTO TABLE tbl_name;

如果，你指定一个LOW_PRIORITY关键字，就不必如上要对表锁定，因为数据的导入将被推迟到没有客户读表为止：

mysql>LOAD DATA  LOW_PRIORITY  INFILE ‘tbl_name’ 
  ->REPLACE INTO TABLE tbl_name;

3、解锁表
mysql>UNLOCK TABLES;



*用直接拷贝的方法备份恢复
备份一个表，需要三个文件：

对于MyISAM表：
tbl_name.frm  表的描述文件
tbl_name.MYD 表的数据文件
tbl_name.MYI  表的索引文件

对于ISAM表：
tbl_name.frm  表的描述文件
tbl_name.ISD 表的数据文件
tbl_name.ISM  表的索引文件

你直接拷贝文件从一个数据库服务器到另一个服务器，对于MyISAM表，你可以从运行在不同硬件系统的服务器之间复制文件，例如，SUN服务器和INTEL PC机之间。

2017-10-20

php SQL 防注入

整理数据库 mysqlcheck -o <databasename>

2017-08-29

Oracle 11g服务器安装

2017-05-01

整理你的数据库
mysqlcheck -o <databasename>

mysql中连接字串用concat('1','a','Z')
select '12'+'34'的结果是46

http://www.cnblogs.com/redfox241/archive/2009/07/23/1529092.html
mysql 中 时间和日期函数