TOMMYHU

linux平台

监控mysql执行的sql语句
为了做好配合开发做性能和功能测试，方便监控正在执行的sql语句，
可以在/etc/mysqld中添加如下：
log =/usr/local/mysql/var21005/mysql.log
就可以使用：
tail -f mysql.log
来监控了  www.tommyhu.cn
如果需要监控慢查询可以添加如下内容：
log-slow-queries = /usr/local/mysql/var21005/slowquery.log
long_query_time = 1

windows平台
修改my.ini，在mysqld下添加log一行，

[mysqld]
log = "D:/tmp/mysql_log/mysql_log.sql"

然后，重新启动mysql，就可以实时看到myql服务器当前正在执行的语句了。

MYSQL临时表关联Can't reopen table，
这是由于


报错实例
  #创建临时表1   DROP TEMPORARY TABLE IF EXISTS `temp_info1`;   CREATE TEMPORARY TABLE `temp_info1`(                 tid           INT auto_increment -- 自增列                 ,starttime      VARCHAR(20)        -- 开始时间                 , PRIMARY KEY(tid)   );   DROP TEMPORARY TABLE IF EXISTS `temp_info2`;   CREATE TEMPORARY TABLE `temp_info2`(                 tid           INT auto_increment -- 自增列                 ,starttime      VARCHAR(20)        -- 开始时间                 , PRIMARY KEY(tid)   ); #debug 以下会报错 /* SELECT a.starttime FROM temp_info1 AS a INNER JOIN temp_info1 AS b ON b.tid=a.tid; */ #正确--曲线救国 -- 复制一份 INSERT temp_info2(starttime) SELECT starttime FROM temp_info1; -- 执行查询 SELECT a.starttime FROM temp_info1 AS a INNER JOIN temp_info2 AS b ON b.tid=a.tid; #销毁临时表 DROP TEMPORARY TABLE IF EXISTS `temp_info1`; DROP TEMPORARY TABLE IF EXISTS `temp_info2`;

MYSQL两个时间相差的月数
/*   算出年间隔*12   算出月间隔   累加 */ set @starttime:='2014-01-28'; set @endtime:='2014-07-21'; select (year(@endtime) - year(@starttime))*12 +  (month(@endtime) - month(@starttime)) as mnth;

连接命令：mysql -h[主机地址] -u[用户名] -p[用户密码]  
创建数据库：create database [库名]  
显示所有数据库: show databases;  
打开数据库:use [库名]  
当前选择的库状态:SELECT DATABASE();  
创建数据表:CREATE TABLE [表名]([字段名] [字段类型]([字段要求]) [字段参数], ......);  
显示数据表字段:describe 表名;  
当前库数据表结构:show tables;  
更改表格　  
  ALTER TABLE [表名] ADD COLUMN [字段名] DATATYPE  
  说明：增加一个栏位（没有删除某个栏位的语法。  
  ALTER TABLE [表名] ADD PRIMARY KEY ([字段名])  
  说明：更改表得的定义把某个栏位设为主键。  
  ALTER TABLE [表名] DROP PRIMARY KEY ([字段名])  
  说明：把主键的定义删除。  
显示当前表字段:show columns from tablename;  
删库:drop database [库名];  
删表:drop table [表名]；  
数据操作  
添加：INSERT INTO [表名] VALUES('','',......顺序排列的数据);  
查询: SELECT * FROM [表名] WHERE ([条件]);  
建立索引:CREATE INDEX [索引文件名] ON [表名] ([字段名]);  
删除：DELETE FROM [表名] WHERE ([条件]);  
修改：UPDATE [表名] SET [修改内容如name = 'Mary'] WHERE [条件]；  

尼玛各种坑爹的节奏，T-SQL应该统一规范

SET @tenantid:=1; -- mssql中写法 UPDATE a SET a.acid=b.newinnerid FROM table1 AS a INNER JOIN table2 AS b ON b.innerid=a.acid WHERE a.tenantid=@tenantid; -- mysql中对应的是 UPDATE table1 a INNER JOIN table2 b ON b.innerid=a.acid SET a.acid=b.newinnerid WHERE a.tenantid=@tenantid;

由于工作的原因：上家公司的数据库全采用MySql，所以不得不用它。因此也学到了MySql的一些知识，但考虑到今后可能没机会使用了，所以想趁现在离职在家休息，打算把这些东西整理一下，也为了万一今后能用上，留个参考的资源。考虑到一直在使用SqlServer，所以就打算直接与SqlServer对比来写。

本文将主要列出MySql与SqlServer不同的地方，且以常用的存储过程的相关内容为主。

1. 标识符限定符
SqlServer  []
MySql  ``

2. 字符串相加
SqlServer  直接用 +
MySql  concat()

3. isnull()
SqlServer  isnull()
MySql  ifnull()

注意：MySql也有isnull()函数，但意义不一样

4. getdate()
SqlServer  getdate()
MySql  now()

5. newid()
SqlServer  newid()
MySql   uuid()

6. @@ROWCOUNT
SqlServer  @@ROWCOUNT
MySql  row_count()

注意：MySql的这个函数仅对于update, insert, delete有效

7. SCOPE_IDENTITY()
SqlServer  SCOPE_IDENTITY()
MySql  last_insert_id()

8. if ... else ...
SqlServer  

IF Boolean_expression
     { sql_statement | statement_block }
[ ELSE
     { sql_statement | statement_block } ]

-- 若要定义语句块，请使用控制流关键字 BEGIN 和 END。

MySql  
IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF
注意：对于MySql来说，then, end if是必须的。类似的还有其它的流程控制语句，这里就不一一列出。

9. declare
其实，SqlServer和MySql都有这个语句，用于定义变量，但差别在于：在MySql中，DECLARE仅被用在BEGIN ... END复合语句里，并且必须在复合语句的开头，在任何其它语句之前。这个要求在写游标时，会感觉很BT.

10. 游标的写法
SqlServer  
declare @tempShoppingCart table (ProductId int, Quantity int) insert into @tempShoppingCart (ProductId, Quantity)   select ProductId, Quantity from ShoppingCart where UserGuid = @UserGuid declare @productId int declare @quantity int declare tempCartCursor cursor for     select ProductId, Quantity from @tempShoppingCart open tempCartCursor fetch next from tempCartCursor into @productId, @quantity while  @@FETCH_STATUS = 0 begin   update Product set SellCount = SellCount + @quantity  where productId = @productId   fetch next from tempCartCursor into @productId, @quantity end close tempCartCursor deallocate tempCartCursor

MySql  
declare m_done int default 0; declare m_sectionId int; declare m_newsId int; declare _cursor_SN cursor for select sectionid, newsid from _temp_SN; declare continue handler for not found set m_done = 1; create temporary table _temp_SN   select sectionid, newsid from SectionNews  group by sectionid, newsid having count(*) > 1; open _cursor_SN; while( m_done = 0 ) do   fetch _cursor_SN into m_sectionId, m_newsId;      if( m_done = 0 ) then     -- 具体的处理逻辑   end if; end while; close _cursor_SN; drop table _temp_SN;
注意：为了提高性能，通常在表变量上打开游标，不要直接在数据表上打开游标。

11. 分页的处理
SqlServer   create procedure GetProductByCategoryId(     @CategoryID int,     @PageIndex int = 0,     @PageSize int = 20,     @TotalRecords int output ) as begin      declare @ResultTable table (     RowIndex int,     ProductID int,     ProductName nvarchar(50),     CategoryID int,     Unit nvarchar(10),     UnitPrice money,     Quantity int );      insert into @ResultTable select row_number() over (order by ProductID asc) as RowIndex,        p.ProductID, p.ProductName, p.CategoryID, p.Unit, p.UnitPrice, p.Quantity from   Products as p where CategoryID = @CategoryID;        select  @TotalRecords = count(*) from  @ResultTable;      select * from   @ResultTable where  RowIndex > (@PageSize * @PageIndex) and RowIndex <= (@PageSize * (@PageIndex+1));      end;
当然，SqlServer中并不只有这一种写法，只是这种写法是比较常见而已。

MySql  
create procedure GetProductsByCategoryId(    in _categoryId int,    in _pageIndex int,    in _pageSize int,    out _totalRecCount int ) begin    set @categoryId = _categoryId;    set @startRow = _pageIndex * _pageSize;    set @pageSize = _pageSize;    prepare PageSql from   'select sql_calc_found_rows * from product  where categoryId = ? order by ProductId desc limit ?, ?';    execute PageSql using @categoryId, @startRow, @pageSize;    deallocate prepare PageSql;    set _totalRecCount = found_rows(); end
MySql与SqlServer的差别实在太多，以上只是列出了我认为经常在写存储过程中会遇到的一些具体的差别之处。

去年我将一些MySql的常用函数作了一番整理

mysql查询上个月的第一天
select date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(      day from now())-1 day),interval 1 month)
最后一天
select date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(      day from now()) day),interval 0 month) as date
这个月的第一天：
select date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(      day from now())-1 day),interval 0 month)
这个月的最后一天：
select date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(      day from now()) day),interval -1 month) as date
至于你要查那一个月啊，now())-1 day 和 interval 0 后面的数字自己去改就行了。
匹配时间区间
unix_timestamp( time ) between unix_timestamp( 'start ') and unix_timestamp( 'end' )

30天内的数据
Select something FROM table　 Where TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;　

MySQL中利用存储过程分割字符串的方法,将函数和存储执行，存储依赖前面的函数，存储出来的结构式一个临时表
# 函数：func_split_TotalLength   DELIMITER $$   DROP function IF EXISTS `func_split_TotalLength` $$   CREATE DEFINER=`root`@`%` FUNCTION `func_split_TotalLength`   (f_string varchar(1000),f_delimiter varchar(5)) RETURNS int(11)   BEGIN       # 计算传入字符串的总length       return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));   END$$   DELIMITER;  
# 函数：func_split DELIMITER $$ DROP function IF EXISTS `func_split` $$ CREATE DEFINER=`root`@`%` FUNCTION `func_split` (f_string varchar(1000),f_delimiter varchar(5),f_order int) RETURNS varchar(255) CHARSET utf8 BEGIN     # 拆分传入的字符串，返回拆分后的新字符串     declare result varchar(255) default '';     set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1));     return result; END$$ DELIMITER;
# 存储过程：splitString DELIMITER $$ DROP PROCEDURE IF EXISTS `splitString` $$ CREATE PROCEDURE `splitString` (IN f_string varchar(1000),IN f_delimiter varchar(5)) BEGIN     # 拆分结果     declare cnt int default 0;     declare i int default 0;     set cnt = func_split_TotalLength(f_string,f_delimiter);     DROP TABLE IF EXISTS `tmp_split`;     create temporary table `tmp_split` (`status` varchar(128) not null) DEFAULT CHARSET=utf8;     while i < cnt     do         set i = i + 1;         insert into tmp_split(`status`) values (func_split(f_string,f_delimiter,i));     end while; END$$ DELIMITER;