Mysql 学习笔记内容很全

mysql-8.0.11-winx64.zip安装教程
2018年05月11日 09:54:19
阅读数：265
下载zip安装包：
　　MySQL8.0 For Windows zip包下载地址：https://dev.mysql.com/downloads/file/?id=476233，进入页面后可以不登录。后点击底部“No thanks, just start my download.”即可开始下载。
环境：Windows 10
一，安装
　　1.1，解压zip包到安装目录
　　比如我的安装目录是：C:\Program Files\MySQL
　　1.2，配置文件
　　在Windows系统中，配置文件默认是安装目录下的 my.ini 文件（或my-default.ini，部分配置需要在初始安装时配置，大部分也可以在安装完成后进行更改。当然，极端情况下，所有的都是可以更改的。
　　我们发现解压后的目录并没my.ini文件，没关系可以自行创建。在安装根目录下添加 my.ini，比如我这里是：C:\Program Files\MySQL\my.ini，写入基本配置：
 
[mysqld]

设置3306端口

port=3306

设置mysql的安装目录

basedir=C:\Program Files\MySQL

设置mysql数据库的数据的存放目录

datadir=E:\database\MySQL\Data

允许最大连接数

max_connections=200

允许连接失败的次数。这是为了防止人从该主机试图攻击数据库系统

max_connect_errors=10

服务端使用的字符集默认为UTF8

character-set-server=utf8

创建新表时将使用的默认存储引擎

default-storage-engine=INNODB
[mysql]

设置mysql客户端默认字符集

default-character-set=utf8
[client]

设置mysql客户端连接服务端时默认使用的端口

port=3306
default-character-set=utf8
 
注意，里面的 basedir 是我本地的安装目录，datadir 是我数据库数据文件要存放的位置，各项配置需要根据自己的环境进行配置。
查看所的配置项，可参考：https://dev.mysql.com/doc/refman/8.0/en/mysqld-option-tables.html
　　1.3，初始化数据库
在MySQL安装目录的 bin 目录下执行命令：
mysqld –initialize –console
执行完成后，会打印 root 用户的初始默认密码，比如：
C:\Users\Administrator>cd C:\Program Files\MySQL\bin

C:\Program Files\MySQL\bin>mysqld –initialize –console
2018-04-28T15:57:17.087519Z 0 [System] [MY-013169] [Server] C:\Program Files\MySQL\bin\mysqld.exe (mysqld 8.0.11) initializing of server in progress as process 4984
2018-04-28T15:57:24.859249Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: rI5rvf5x5G,E
2018-04-28T15:57:27.106660Z 0 [System] [MY-013170] [Server] C:\Program Files\MySQL\bin\mysqld.exe (mysqld 8.0.11) initializing of server has completed

C:\Program Files\MySQL\bin>
 
　　注意！执行输出结果里面有一段： [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: rI5rvf5x5G,E 其中root@localhost:后面的“rI5rvf5x5G,E”就是初始密码（不含首位空格。在没更改密码前，需要记住这个密码，后续登录需要用到。
　　要是你手贱，关快了，或者没记住，那也没事，删掉初始化的 datadir 目录，再执行一遍初始化命令，又会重新生成的。当然，也可以使用安全工具，强制改密码，用什么方法，自己随意。
 
　　1.4，安装服务
在MySQL安装目录的 bin 目录下执行命令（以管理员身份打开cmd命令行，或者在安装目录Shift+右键“在此处打开命令行窗口”：
mysqld –install [服务名]
后面的服务名可以不写，默认的名字为 mysql。当然，如果你的电脑上需要安装多个MySQL服务，就可以用不同的名字区分了，比如 mysql5 和 mysql8。
安装完成之后，就可以通过命令net start mysql启动MySQL的服务了。
示例：
C:\Program Files\MySQL\bin>mysqld –install
Service successfully installed.

C:\Program Files\MySQL\bin>net start mysql
MySQL 服务正在启动 ..
MySQL 服务已经启动成功。

C:\Program Files\MySQL\bin>
 
二，更改密码和密码认证插件
 　　在MySQL安装目录的 bin 目录下执行命令：
mysql -u root -p
　　这时候会提示输入密码，记住了上面第1.3步安装时的密码，填入即可登录成功，进入MySQL命令模式。
在MySQL8.0.4以前，执行
SET PASSWORD=PASSWORD(‘[修改的密码]’);
就可以更改密码，但是MySQL8.0.4开始，这样默认是不行的。因为之前，MySQL的密码认证插件是“mysql_native_password”，而现在使用的是“caching_sha2_password”。
　　因为当前有很多数据库工具和链接包都不支持“caching_sha2_password”，为了方便，我暂时还是改回了“mysql_native_password”认证插件。
在MySQL中执行命令：
ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘新密码’;
修改密码验证插件，同时修改密码。
　　如果想默认使用“mysql_native_password”插件认证，可以在配置文件中配置default_authentication_plugin项。
[mysqld]
default_authentication_plugin=mysql_native_password
示例：
C:\Program Files\MySQL\bin>mysql -u root -p
Enter password: **
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.11

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘新密码’;
Query OK, 0 rows affected (0.06 sec)

mysql>

恢复MySQL数据库创建存储过程是遇到错误

you might want to use the less safe log_bin_trust_function_creators variable

需要执行

SET GLOBAL log_bin_trust_function_creators = 1;

如何查看MySQL的当前存储引擎？
如何查看MySQL的当前存储引擎？
一般情况下，mysql会默认提供多种存储引擎,你可以通过下面的查看:
 
看你的mysql现在已提供什么存储引擎:
mysql> show engines;
 
看你的mysql当前默认的存储引擎:
mysql> show variables like ‘%storage_engine%’;
 
你要看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎):
mysql> show create table 表名;
 
 lower_case_table_names = 0
如何查看Mysql服务器上的版本
额 系统函数啊
select version();
 
代码才帅气
 
Mysql数据库3种存储引擎有什么区别？
这个是考虑性能的问题，还事务的支持，吧   百度一下你就知道
 
MyISAM、InnoDB、Heap(Memory)、NDB 
 
貌似一般都是使用   InnoDB的，
 
mysql的存储引擎包括：MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDBCluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事务安全表，其他存储引擎都是非事务安全表。
最常使用的2种存储引擎:
1.Myisam是Mysql的默认存储引擎，当create创建新表时，未指定新表的存储引擎时，默认使用Myisam。每个MyISAM在磁盘上存储成三个文件。文件名都和表名相同，扩展名分别是.frm（存储表定义）、.MYD(MYData，存储数据)、.MYI(MYIndex，存储索引)。数据文件和索引文件可以放置在不同的目录，平均分布io，获得更快的速度。
2.InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比Myisam的存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
 
不知道是不是对你帮助
 
提问者评价谢谢！

 

修改mysql的默认存储引擎
 
1、查看mysql存储引擎命令，
在mysql>提示符下搞入show engines;
字段 Support为:Default表示默认存储引擎  
  www.2cto.com  
2、设置InnoDB为默认引擎：
在配置文件my.cnf中的 [mysqld] 下面加入
default-storage-engine=INNODB 一句
 
3、重启mysql服务器：
mysqladmin -u root -p shutdown
或者service mysqld restart 登录mysql数据库
 
 
MySQL查看和修改表的存储引擎
1 查看系统支持的存储引擎
show engines;
2 查看表使用的存储引擎
两种方法：
a、show table status from db_name where name=’table_name’;
b、show create table table_name;
如果显示的格式不好看，可以用\g代替行尾分号
人说用第二种方法不准确，我试了下，关闭掉原先默认的Innodb引擎后根本无法执行show create table table_name指令，因为之前建的是Innodb表，关掉后默认用MyISAM引擎，导致Innodb表数据无法被正确读取。
3 修改表引擎方法
alter table table_name engine=innodb;
4 关闭Innodb引擎方法
关闭mysql服务： net stop mysql
找到mysql安装目录下的my.ini文件：
找到default-storage-engine=INNODB 改为default-storage-engine=MYISAM
找到#skip-innodb 改为skip-innodb
启动mysql服务：net start mysql
 
 
MySql版本问题sql_mode=only_full_group_by的完美解决方案

set @@GLOBAL.sql_mode=”;
set sql_mode =’STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION’;
commit;

MySQL 5.7.9版本sql_mode=only_full_group_by问题
用到GROUP BY 语句查询时com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘col_user_6.a.START_TIME’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by错误
解决方法 ：执行SET GLOBAL sql_mode = ”; 把sql_mode 改成非only_full_group_by模式。验证是否生效 SELECT @@GLOBAL.sql_mode 或 SELECT @@sql_mode

SET sql_mode =’STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;

 

查询某表所有字段内容：

sELECT *
FROM information_schema.columns
WHERE table_schema = ‘ictms’ and table_name=’ictms_reservel_order_if_h’
ORDER BY table_name DESC;

select distinct t.table_name from information_schema.COLUMNS t
where 1=1
and t.table_name like ‘ictms%’

mysql 查看锁表解锁
– 查看那些表锁到了
show OPEN TABLES where In_use > 0;
– 查看进程号
show processlist;
–删除进程
 kill 1085850；

MySQL中有5个函数需要计算当前时间的值：
NOW.返回时间，格式如：2012-09-23 06:48:28
CURDATE，返回时间的日期，格式如：2012-09-23
CURTIME，返回时间，格式如：06:48:28
UNIX_TIMESTAMP，返回时间整数戳，如：1348408108
SYSDATE，返回时间，格式和time()函数返回时间一样，但是有区别。
除了本身定义所返回的区别以外，另一个区别是：前四个函数都是返回基于语句的开始执行时间，而SYSDATE返回time的值。
通过比较，可以发现这两个函数的区别：
NOW()执行如下：
mysql> select now(),sleep(2),now();  
+———————+———-+———————+   
| now()               | sleep(2) | now()               |  
+———————+———-+———————+   
| 2012-09-23 06:54:29 |        0 | 2012-09-23 06:54:29 |   
+———————+———-+———————+   
1 row in set (2.00 sec)  
其返回的两个值是一样的，因为都是表示语句开始执行的时间。
SYSDATE执行如下：
mysql> select sysdate(),sleep(2),sysdate();  
+———————+———-+———————+   
| sysdate()           | sleep(2) | sysdate()           |  
+———————+———-+———————+   
| 2012-09-23 06:55:00 |        0 | 2012-09-23 06:55:02 |   
+———————+———-+———————+   
1 row in set (2.01 sec)  
也正因为这个区别，我们一般在执行语句的时候，都是用NOW()，因为SYSDATE获取当时实时的时间，这有可能导致主库和从库是执行的返回值是不一样的，导致主从数据不一致。
其上其它函数执行如下：
mysql> select now(),sysdate(),curdate(),curtime(),unix_timestamp()\G;  
***************** 1. row *****************  
           now(): 2012-09-23 07:00:05  
       sysdate(): 2012-09-23 07:00:05  
       curdate(): 2012-09-23  
       curtime(): 07:00:05  
unix_timestamp(): 1348408805  
1 row in set (0.00 sec)  

DROP FUNCTION IF EXISTS F_GET_COM_NAME_BY_CODE;
DELIMITER $$
create function F_GET_COM_NAME_BY_CODE(P_COM_CD varchar(30)) returns int
begin
select count(*) into @result from ictms.tb_code_info t
where t.COM_CODE = P_COM_CD;
return @result;

end $$
DELIMITER;

DROP FUNCTION IF EXISTS F_GET_COM_NAME_BY_CODE;
DELIMITER $$
create function F_GET_COM_NAME_BY_CODE(P_EAN varchar(30),P_ITEM_CD varchar(30),P_ITEM_TP varchar(30)) returns VARCHAR(200)
begin
select count(*) into @result from ictms.tb_code_info t
where t.COM_CODE = P_COM_CD;
return @result;

end $$
DELIMITER;

DROP PROCEDURE IF EXISTS F_GET_COM_NAME_BY_CODE;
DELIMITER $$
create PROCEDURE F_GET_COM_NAME_BY_CODE(P_EAN varchar(30),P_ITEM_CD varchar(30),P_ITEM_TP varchar(30)) returns VARCHAR(200)
begin
select count(*) into @result from ictms.tb_code_info t
where t.COM_CODE = P_COM_CD;
return @result;

end $$
DELIMITER;

select F_GET_COM_NAME_BY_CODE(‘LGCNS’)

mysql相似于oracle的to_char() to_date()方法  
2013-06-28 18:05:48|  分类: mysql|举报|字号 订阅
  下载LOFTER我的照片书  |
本文转自:http://blog.sina.com.cn/s/blog_68f4b9f201013vql.html
 
mysql日期和字符相互转换方法
date_format(date,’%Y-%m-%d’) ————–>oracle中的to_char();
str_to_date(date,’%Y-%m-%d’) ————–>oracle中的to_date();

%Y:代表4位的年份
%y:代表2为的年份
%m:代表月, 格式为(01……12)
%c:代表月, 格式为(1……12)
%d:代表月份中的天数,格式为(00……31)
%e:代表月份中的天数, 格式为(0……31)
%H:代表小时,格式为(00……23)
%k:代表 小时,格式为(0……23)
%h: 代表小时,格式为(01……12)
%I: 代表小时,格式为(01……12)
%l :代表小时,格式为(1……12)
%i: 代表分钟, 格式为(00……59)

%r:代表 时间,格式为12 小时(hh:mm:ss [AP]M)
%T:代表 时间,格式为24 小时(hh:mm:ss)

%S:代表 秒,格式为(00……59)
%s:代表 秒,格式为(00……59)
 

 

日期比较示例:
 
SELECT * FROM test_test1 where birth_daetime=’2013/06/28 17:47:52’
SELECT * FROM test_test1 where birth_day=’2013/06/28’
SELECT * FROM test_test1 where birth_day=’2013-06-28’
SELECT * FROM test_test1 where birth_daetime=str_to_date(‘2013-06-28 17:47:52’, ‘%Y-%m-%d %H:%i:%s’)
SELECT * FROM test_test1 where birth_day=str_to_date(‘2013/06/28’, ‘%Y/%m/%d’)
SELECT * FROM test_test1 where birth_day=str_to_date(‘2013-06-28 00:00:00’, ‘%Y-%m-%d %H:%i:%s’)

DROP FUNCTION IF EXISTS F_GET_COM_NAME_BY_CODE;
DELIMITER $$
create function F_GET_COM_NAME_BY_CODE(P_COM_CD varchar(30),P_DIVISION_CD VARCHAR(30),P_CODE VARCHAR(30),P_LANG VARCHAR(20)) returns VARCHAR(200)
begin
SELECT CASE
WHEN UPPER(P_LANG) = ‘EN’ THEN
CODE_NM
ELSE
CODE_NM_ZH
END
INTO @result
FROM TB_CODE_INFO B
WHERE B.COM_CODE = P_COM_CD
AND B.DIVISION = P_DIVISION_CD
AND B.CODE = P_CODE
AND B.DEL_YN = ‘N’;
return @result;

end $$
DELIMITER;

select F_GET_COM_NAME_BY_CODE(‘LGCNS’,’PKG_FACTORY’, ‘LG’, ‘zh’)

ALTER TABLE ictms.ictms_reservel_order_masta
ADD COLUMN FST_REG_USER_ID varchar(30) COMMENT ‘FIRST REGIST USER ID’;

ALTER TABLE ictms.ictms_reservel_order_masta
ADD COLUMN FST_REG_USER_ID varchar(30) COMMENT ‘FIRST REGIST USER ID’,
ADD COLUMN FST_REG_NM varchar(200) COMMENT ‘FIRST REGIST USER NAME’,
ADD COLUMN FST_REG_DT datetime(0) DEFAULT NOW() COMMENT ‘FIRST REGIST USER NAME’,
ADD COLUMN LST_MOD_USER_ID varchar(30) COMMENT ‘LAST UPDATE USER NAME’,
ADD COLUMN LST_MOD_NM varchar(200) COMMENT ‘LAST UPDATE USER NAME’,
ADD COLUMN LST_MOD_DT datetime(0) DEFAULT NOW() COMMENT ‘LAST UDPATE DATE’,
ADD COLUMN USE_YN varchar(1) DEFAULT ‘Y’ COMMENT ‘Y:USE,N:NO USE’,

ictms_custom_if_h
ictms_custom_mst
ictms_item_if_h
ictms_item_mst
ictms_reservel_order_if_h
ictms_reservel_order_masta
ictms_reservel_po_if_h
ictms_reservel_po_masta

Oracle to Mysql（转）
Oracle to Mysql 数据库移植方案
 
系统移植方案
 
Mysql的版本是mysql-5.1.19-win32
操作系统是WINXP2

1.1 移植过程中重点问题
1.1.1 数据类型差异
ORACLE数据库和MYSQL数据库在数据类型方面差异比较大，而且数据类型也是一个数据库存储数据的基础，所以找到数据类型之间的对应是整个系统进行移植的基础。以下给出了ORACLE à MYSQL数据类型的对应关系。
 
数值类型：
 
   NUMBER à DECIMAL，精度刻度都不变
 
注：如果是序列用BIGINT
 
字符串类型：
 
   VARCHAR2 à VARCHAR长度不变。
   LONG à LONGTEXT
 
这里可能遇到的问题是超过主键key长度的问题，根据实际情况适当修改，如果是TEXT类型也需要指名长度，否则建立key会报错
 
日期类型：
 
   DATE à DATETIME
   TIMESTAMP(N) à TIMESTAMP
 

1.1.2 SQL语法差异
SEQUENCE：
 
MYSQL没有ORACLE中的SEQUENCE对象，我们在迁移的时候需要特别注意，一般SEQUENCE有两种用途：
1、 作为表中自增字段的序列号。
2、 程序中获得自动编号。
 
MYSQL数据类型中存在 AUTO_INCREMENT为自增数据类型。我们可以利用该数据类型变通一下来满足我们现有系统中的SEQUENCE功能。
1、 对于ORACLE中SEQUENCE作为表的自增列一般是通过与触发器绑定实现的，在MYSQL中我们可以直接利用MYSQL的AUTO_INCREMENT类型来实现。
2、 ORACLE开发的应用程序中直接SELECT SEQUENCT来获得自动编号，对于这个功能我们也可以利用MYSQL的AUTO_INCREMENT类型来实现。
 
首先介绍一个函数，我们可以利用如下函数查询最后一个序列号的值：
 
mysql> SELECT LAST_INSERT_ID();
+——————+
| LAST_INSERT_ID() |
+——————+
|                3 |
+——————+
1 row in set (0.06 sec)
 
我们可以创建一个含有自增列的表，对该表进行INSERT操作后，再利用LAST_INSERT_ID()函数来获得刚刚INSERT的值，也就是相当于ORACLE中的SEQUENCE. NETVAL。也就是INSERT操作+SELECT操作获得一个自动编号。
 
mysql> CREATE TABLE MOCHA_BE_SEQUENCE(ID BIGINT NOT NULL PRIMARY KEY AUTO_INCREM
ENT);
Query OK, 0 rows affected (0.63 sec)
 
mysql> INSERT INTO MOCHA_BE_SEQUENCE VALUES(NULL);
Query OK, 1 row affected (0.09 sec)
 
mysql> SELECT LAST_INSERT_ID();
+——————+
| LAST_INSERT_ID() |
+——————+
|                1 |
+——————+
1 row in set (0.05 sec)
 
mysql> INSERT INTO MOCHA_BE_SEQUENCE VALUES(NULL);
Query OK, 1 row affected (0.06 sec)
 
mysql> SELECT LAST_INSERT_ID();
+——————+
| LAST_INSERT_ID() |
+——————+
|                2 |
+——————+
1 row in set (0.00 sec)
 
BLOG：
 
ORACLE和MYSQL都支持二进制大对象，数据类型的名称都是BLOB，在存储方面都是一样的，BLOB列没有字符集，并且排序和比较基于列值字节位数；在开发应用程序时需要注意对两种数据库BLOG类型的操作的差异。
 
视图
 
Mysql视图限制（1）SELECT语句不能包含FROM子句中的子查询。
（2）SELECT语句不能引用系统或用户变量。
（3）SELECT语句不能引用预处理语句参数。
（4）在存储子程序内，定义不能引用子程序参数或局部变量。
（5）在定义中引用的表或视图必须存在。但是，创建了视图后，能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题，可使用CHECK TABLE语句。
（6）在定义中不能引用TEMPORARY表，不能创建TEMPORARY视图。
（7）在视图定义中命名的表必须已存在。
（8）不能将触发程序与视图关联在一起。
 
我所要迁移的系统中的视图90%用到了子查询，解决方案是首先重新写查询语句，尽量避免子查询，避免不了，就将子查询中的内容，单独create成一个新的视图，然后再建立所需要的视图。
 
例子：
 
Oracle中带子查询的视图：
 
CREATE OR REPLACE VIEW MOCHA_IM_ALL_ACCOUNT_VIEW AS
SELECT USER_ID AS ID,
USER_NAME AS NAME,
CONCAT(CONCAT(CONCAT(NVL(ORG.ORG_CODE, ‘00000001.10000000’), ‘.00.’),
PERSON_POSITION.PERSON_LEVEL),
LPAD(PERSON_POSITION.ORG_PERSON_NO, 9, ‘0’)) AS CODE,
NVL(ORG.ORG_LEVEL, 1) AS REC_LEVEL,
‘PERSON’ AS REC_TYPE
FROM (SELECT PERSON.USER_ID,
PERSON.USER_NAME,
NVL(POSITION.ORG_ID, -1) AS ORG_ID,
NVL(POSITION.ORG_PERSON_NO, 0) AS ORG_PERSON_NO,
NVL(POSITION.PERSON_LEVEL, ‘64’) AS PERSON_LEVEL
FROM MOCHA_IM_PERSON_POSITION POSITION, MOCHA_IM_PERSON PERSON
WHERE PERSON.USER_ID = POSITION.USER_ID(+)
AND PERSON.ADMIN_FLAG = ‘0’
AND PERSON.STATUS = ‘A’) PERSON_POSITION,
MOCHA_IM_ORG_VIEW ORG
WHERE PERSON_POSITION.ORG_ID = ORG.ORG_ID(+)
UNION ALL
SELECT USER_ID AS ID,
USER_NAME AS NAME,
‘00000001.20000000.00.64000000000’ AS CODE,
1 AS REC_LEVEL,
‘PERSON’ AS REC_TYPE
FROM MOCHA_IM_PERSON PERSON
WHERE PERSON.ADMIN_FLAG = ‘0’
AND PERSON.STATUS = ‘I’
UNION ALL
SELECT USER_ID AS ID,
USER_NAME AS NAME,
‘00000001.30000000.00.64000000000’ AS CODE,
1 AS REC_LEVEL,
‘PERSON’ AS REC_TYPE
FROM MOCHA_IM_PERSON
WHERE ADMIN_FLAG = ‘1’
AND STATUS = ‘A’
UNION ALL
SELECT LPAD(ORG_ID, 20, ’ ‘) AS ID,
ORG_NAME AS NAME,
ORG_CODE AS CODE,
ORG_LEVEL AS REC_LEVEL,
‘ORG’ AS REC_TYPE
FROM MOCHA_IM_ORG_VIEW
UNION ALL
SELECT ‘-1’ AS ID,
‘未分派人员’ AS NAME,
✀ ‘00000001.10000000’ AS CODE,
1 AS REC_LEVEL,
‘ORG’ AS REC_TYPE
FROM DUAL
UNION ALL
SELECT ‘-2’ AS ID,
‘待删除人员’ AS NAME,
‘00000001.20000000’ AS CODE,
1 AS REC_LEVEL,
‘ORG’ AS REC_TYPE
FROM DUAL
UNION ALL
SELECT ‘-3’ AS ID,
‘系统管理员’ AS NAME,
‘00000001.30000000’ AS CODE,
1 AS REC_LEVEL,
‘ORG’ AS REC_TYPE
FROM DUAL
/
 
转为mysql：
 
CREATE OR REPLACE VIEW PERSON_POSITION AS
SELECT PERSON.USER_ID ,
 PERSON.USER_NAME,
 IFNULL (POSITION.ORG_ID,   - 1)  AS ORG_ID,
 IFNULL (POSITION.ORG_PERSON_NO,  0)  AS ORG_PERSON_NO,
 IFNULL (
POSITION.PERSON_LEVEL,
  ‘64’
)  AS PERSON_LEVEL  FROM MOCHA_IM_PERSON_POSITION POSITION LEFT JOIN  MOCHA_IM_PERSON PERSON ON PERSON.USER_ID  =  POSITION.USER_ID  WHERE PERSON.ADMIN_FLAG  =   ‘0’  And PERSON. STATUS = ‘A’;
/
 
CREATE OR REPLACE VIEW MOCHA_IM_ALL_ACCOUNT_VIEW AS
SELECT USER_ID AS ID,
 USER_NAME AS NAME,
CONCAT(
CONCAT(
CONCAT(
IFNULL(
ORG.ORG_CODE,
‘00000001.10000000’
),
  ‘.00.’
),
PERSON_POSITION.PERSON_LEVEL
),
 LPAD (
PERSON_POSITION.ORG_PERSON_NO,
9,
‘0’
)
)  AS CODE,
  IFNULL(ORG.ORG_LEVEL,  1)  AS REC_LEVEL,
  ‘PERSON’  AS REC_TYPE FROM MOCHA_IM_ORG_VIEW ORG LEFT JOIN  PERSON_POSITION ON PERSON_POSITION.ORG_ID  =  ORG.ORG_ID  UNION ALL SELECT USER_ID AS ID,
 USER_NAME AS NAME,
  ‘00000001.20000000.00.64000000000’  AS CODE,
 1 AS REC_LEVEL,
  ‘PERSON’  AS REC_TYPE FROM MOCHA_IM_PERSON PERSON WHERE PERSON.ADMIN_FLAG  =   ‘0’  And PERSON. STATUS = ‘I’ UNION ALL SELECT USER_ID AS ID,
 USER_NAME AS NAME,
  ‘00000001.30000000.00.64000000000’  AS CODE,
1 AS REC_LEVEL,
  ‘PERSON’  AS REC_TYPE FROM MOCHA_IM_PERSON WHERE ADMIN_FLAG  =   ‘1’  AND STATUS = ‘A’ UNION ALL SELECT LPAD (ORG_ID, 20, ’ ‘)  AS ID,
 ORG_NAME AS NAME,
 ORG_CODE AS CODE,
ORG_LEVEL AS REC_LEVEL,
  ‘ORG’  AS REC_TYPE FROM MOCHA_IM_ORG_VIEW UNION ALL  SELECT  ‘-1’  AS ID,
  ‘未分派人员’  AS NAME,
  ‘00000001.10000000’  AS CODE,
 1 AS REC_LEVEL,
  ‘ORG’  AS REC_TYPE FROM DUAL UNION ALL  SELECT  ‘-2’  AS ID,
  ‘待删除人员’  AS NAME,
  ‘00000001.20000000’  AS CODE,
 1 AS REC_LEVEL,
  ‘ORG’  AS REC_TYPE FROM DUAL UNION ALL  SELECT  ‘-3’  AS ID,
  ‘系统管理员’  AS NAME,
  ‘00000001.30000000’  AS CODE,
 1 AS REC_LEVEL,
   ‘ORG’  AS REC_TYPE From DUAL
/
 
触发器，函数，存储过程
 
语法的区别，难点在于异常处理模块，其他要关注的比如定义游标的语法，mysql控制流程等等。
对于触发器来讲对于具相同触发程序动作时间和事件的给定表，不能两个触发程序。例如，对于某一表，不能有两个BEFORE UPDATE触发程序。但可以有1个BEFORE UPDATE触发程序和1个BEFORE INSERT触发程序，或1个BEFORE UPDATE触发程序和1个AFTER UPDATE触发程序。就是说mysql不支持oracle中create trigger * before insert or 
Update or delete on *.此时应该把这个触发器拆分为3个触发器。
 
例子：
 
Oracle下触发器：
 
CREATE OR REPLACE TRIGGER MOCHA_IM_DUTY_TRG
BEFORE INSERT OR UPDATE OR DELETE ON MOCHA_IM_DUTY
FOR EACH ROW
DECLARE
befImg VARCHAR2(2000);
afterImg VARCHAR2(2000);
action VARCHAR2(10);
action_module VARCHAR2(10);
info_category VARCHAR2(20);
BEGIN
action_module := ‘IM’;
info_category := ‘duty’;
befImg := ‘[DUTY_ID]’ || :old.duty_id || ‘[DUTY_NAME]’ || :old.duty_name;
afterImg := ‘[DUTY_ID]’ || :new.duty_id || ‘[DUTY_NAME]’ || :new.duty_name;

IF (:old.duty_id IS NULL) THEN
    action := 'Insert';
    befImg := '';
    INSERT INTO MOCHA_IM_AUDIT_LOG
        (ACTION_TIMESTAMP, ACTION, ACTION_MODULE, INFO_CATEGORY, BEFORE_IMAGE, AFTER_IMAGE)
    VALUES
        (SYSDATE, action, action_module, info_category, befImg, afterImg);
ELSIF (:new.duty_id IS NULL) THEN
    action   := 'Delete';
    afterImg := '';
    INSERT INTO MOCHA_IM_AUDIT_LOG
        (ACTION_TIMESTAMP, ACTION, ACTION_MODULE, INFO_CATEGORY, BEFORE_IMAGE, AFTER_IMAGE)
    VALUES
        (SYSDATE, action, action_module, info_category, befImg, afterImg);
ELSE
    action := 'Update';
    IF (befImg != afterImg) THEN
        INSERT INTO MOCHA_IM_AUDIT_LOG
            (ACTION_TIMESTAMP, ACTION, ACTION_MODULE, INFO_CATEGORY, BEFORE_IMAGE, AFTER_IMAGE)
        VALUES
            (SYSDATE, action, action_module, info_category, befImg, afterImg);
    END IF;
END IF;

EXCEPTION
WHEN OTHERS THEN
befImg := ”;
END;
/
mysql下：
 CREATE TRIGGER MOCHA_IM_DUTY_TRG_INSERT BEFORE INSERT ON MOCHA_IM_DUTY FOR EACH ROW
BEGIN
DECLARE
befImg VARCHAR (2000);
DECLARE
afterImg VARCHAR (2000);
DECLARE
action VARCHAR (10);
DECLARE
action_module VARCHAR (10);
DECLARE
info_category VARCHAR (20);
DECLARE
EXIT HANDLER FOR SQLEXCEPTION,
SQLWARNING,
NOT FOUND
BEGIN
SET befImg = ”;
END;

SET action_module = ‘IM’;
SET info_category = ‘duty’;

SET afterImg = CONCAT(
‘[DUTY_ID]’,
new.duty_id,
‘[DUTY_NAME]’,
new.duty_name
);
SET action = ‘Insert’;
SET befImg = ”;
INSERT INTO MOCHA_IM_AUDIT_LOG (
ACTION_TIMESTAMP,
ACTION,
ACTION_MODULE,
INFO_CATEGORY,
BEFORE_IMAGE,
AFTER_IMAGE
)
VALUES
(
now(),
action,
action_module,
info_category,
befImg,
afterImg
);

END;
/
 
 
CREATE  TRIGGER MOCHA_IM_DUTY_TRG_DEL BEFORE DELETE ON MOCHA_IM_DUTY FOR EACH ROW
BEGIN
DECLARE befImg VARCHAR (2000);

DECLARE afterImg VARCHAR (2000);

DECLARE action VARCHAR (10);

DECLARE action_module VARCHAR (10);

DECLARE info_category VARCHAR (20);

DECLARE exit HANDLER FOR SQLEXCEPTION,
SQLWARNING,
NOT FOUND
BEGIN
set befImg  =   ”;

END;

set action_module  =   ‘IM’;

set info_category  =   ‘duty’;

set befImg  =  CONCAT (
‘[DUTY_ID]’  ,
 old.duty_id ,
  ‘[DUTY_NAME]’  ,
 old.duty_name
);

set action  =   ‘Delete’;

set afterImg  =   ”;

INSERT INTO MOCHA_IM_AUDIT_LOG  (
ACTION_TIMESTAMP,
 ACTION,
 ACTION_MODULE,
 INFO_CATEGORY,
 BEFORE_IMAGE,
 AFTER_IMAGE
)  VALUES  (
now(),
 action,
 action_module,
 info_category,
 befImg,
 afterImg
);

END;
/
 
CREATE  TRIGGER MOCHA_IM_DUTY_TRG_UPDATE BEFORE  UPDATE ON MOCHA_IM_DUTY FOR EACH ROW
BEGIN
DECLARE befImg VARCHAR (2000);

DECLARE afterImg VARCHAR (2000);

DECLARE action VARCHAR (10);

DECLARE action_module VARCHAR (10);

DECLARE info_category VARCHAR (20);

DECLARE exit HANDLER FOR SQLEXCEPTION,
SQLWARNING,
NOT FOUND
BEGIN
set befImg  =   ”;

END;

set action_module  =   ‘IM’;

set info_category  =   ‘duty’;

set befImg  =  CONCAT (
‘[DUTY_ID]’  ,
 old.duty_id ,
  ‘[DUTY_NAME]’  ,
 old.duty_name
);

set afterImg  = CONCAT(
  ‘[DUTY_ID]’  ,
 new.duty_id ,
  ‘[DUTY_NAME]’  ,
 new.duty_name
);

  IF  (old.duty_id is null)  THEN set action  =   ‘Insert’;

set befImg  =   ”;

ELSEIF  (new.duty_id is null)  THEN   set action  =   ‘Delete’;

set afterImg  =   ”;

ELSE
set action  =   ‘Update’;

IF (befImg  !=  afterImg)  THEN     INSERT INTO MOCHA_IM_AUDIT_LOG  (
ACTION_TIMESTAMP,
 ACTION,
 ACTION_MODULE,
 INFO_CATEGORY,
 BEFORE_IMAGE,
 AFTER_IMAGE
)  VALUES  (
now(),
 action,
 action_module,
 info_category,
 befImg,
 afterImg
);

END IF;

END IF;

END;
/
 
在声过程和函数时注意过程支持inout参数函数不支持，mysql下函数是returns
 
Oracle下过程：
 CREATE OR REPLACE PROCEDURE MOCHA_FE_OPEN_DOC_SP(v_sql VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE ’ insert into MOCHA_FE_DOC_BODY_TEMP ’ || v_sql;
END MOCHA_FE_OPEN_DOC_SP;
/
Mysql下：
create procedure MOCHA_FE_OPEN_DOC_SP (v_sql VARCHAR(200))
BEGIN
set @sqltext = CONCAT(
’ insert into MOCHA_FE_DOC_BODY_TEMP ‘,
v_sql
);

prepare stmt from @sqltext;

execute stmt;

end ;
/
 
Oracle下函数：
 
CREATE OR REPLACE FUNCTION GET_PER_NAME_LEVEL(v_UserId VARCHAR2) RETURN VARCHAR2 IS
v_UserName VARCHAR2(30);
v_PrimaryPosition VARCHAR2(1);
v_PersonLevel VARCHAR2(2);
v_LevelName VARCHAR2(4000);
v_Result VARCHAR2(4000);
v_RecCount NUMBER;
CURSOR c_PersonPosition IS
SELECT PERSON.USER_NAME,
POSITION.PERSON_LEVEL,
POSITION.PRIMARY_POSITION,
PERSON_LEVEL.LEVEL_NAME
FROM MOCHA_IM_PERSON PERSON,
MOCHA_IM_PERSON_POSITION POSITION,
MOCHA_IM_PERSON_LEVEL PERSON_LEVEL
WHERE PERSON.USER_ID = POSITION.USER_ID
AND POSITION.PERSON_LEVEL = PERSON_LEVEL.PERSON_LEVEL
AND POSITION.USER_ID = v_UserId
ORDER BY POSITION.PRIMARY_POSITION DESC;
BEGIN
SELECT COUNT(PERSON_LEVEL)
INTO v_RecCount
FROM MOCHA_IM_PERSON_POSITION POSITION
WHERE USER_ID = v_UserId;
IF v_RecCount = 0 THEN
SELECT USER_NAME INTO v_Result FROM MOCHA_IM_PERSON PERSON WHERE USER_ID = v_UserId;
ELSE
OPEN c_PersonPosition;

    LOOP
        FETCH c_PersonPosition
            INTO v_UserName, v_PersonLevel, v_PrimaryPosition, v_LevelName;
        EXIT WHEN c_PersonPosition%NOTFOUND;

        v_Result := NVL(v_Result, CONCAT(CONCAT(v_Result, v_UserName), '['));

        IF (v_PrimaryPosition = '1' AND (v_RecCount > 1 OR SUBSTR(v_PersonLevel, 2) < '3')) THEN
            v_Result := CONCAT(v_Result, v_LevelName);
        ELSIF (v_RecCount > 0 AND v_PrimaryPosition = '0') THEN
            v_Result := CONCAT(CONCAT(v_Result, '兼'), v_LevelName);
        END IF;
    END LOOP;
    CLOSE c_PersonPosition;
    IF LENGTH(v_Result) > 0 THEN
        v_Result := CONCAT(v_Result, ']');
    END IF;
    IF SUBSTR(v_Result, LENGTH(v_Result) - 1) = '[]' THEN
        v_Result := SUBSTR(v_Result, 1, LENGTH(v_Result) - 2);
    END IF;
END IF;
RETURN v_Result;

END;
 
Mysql下函数：
 
CREATE FUNCTION GET_PER_NAME_LEVEL (v_UserId VARCHAR(30)) RETURNS VARCHAR (30)
BEGIN
DECLARE
v_UserName VARCHAR (30);

DECLARE
v_PrimaryPosition VARCHAR (1);

DECLARE
v_PersonLevel VARCHAR (2);

DECLARE
v_LevelName VARCHAR (4000);

DECLARE
v_Result VARCHAR (4000);

DECLARE
v_RecCount DECIMAL;

DECLARE
c_PersonPosition CURSOR FOR SELECT
PERSON.USER_NAME,
POSITION.PERSON_LEVEL,
POSITION.PRIMARY_POSITION,
PERSON_LEVEL.LEVEL_NAME
FROM
MOCHA_IM_PERSON PERSON,
MOCHA_IM_PERSON_POSITION POSITION,
MOCHA_IM_PERSON_LEVEL PERSON_LEVEL
WHERE
PERSON.USER_ID = POSITION.USER_ID
AND POSITION.PERSON_LEVEL = PERSON_LEVEL.PERSON_LEVEL
AND POSITION.USER_ID = v_UserId
ORDER BY
POSITION.PRIMARY_POSITION DESC;

SELECT
COUNT(PERSON_LEVEL) INTO v_RecCount
FROM
MOCHA_IM_PERSON_POSITION POSITION
WHERE
USER_ID = v_UserId;

IF v_RecCount = 0 THEN
SELECT
USER_NAME INTO v_Result
FROM
MOCHA_IM_PERSON PERSON
WHERE
USER_ID = v_UserId;

ELSE
OPEN c_PersonPosition;

REPEAT
FETCH c_PersonPosition INTO v_UserName,
v_PersonLevel,
v_PrimaryPosition,
v_LevelName;

IF NOT done THEN

SET v_Result = IFNULL(
v_Result,
CONCAT(
CONCAT(v_Result, v_UserName),
‘[’
)
);

IF (
v_PrimaryPosition = ‘1’
AND (
v_RecCount > 1
OR SUBSTR(v_PersonLevel, 2) < ‘3’
)
) THEN

SET v_Result = CONCAT(v_Result, v_LevelName);

ELSEIF (
v_RecCount > 0
AND v_PrimaryPosition = ‘0’
) THEN

SET v_Result = CONCAT(
CONCAT(v_Result, ‘兼’),
v_LevelName
);

END
IF;

END
IF;

UNTIL done
END
REPEAT
;

CLOSE c_PersonPosition;

IF LENGTH(v_Result) > 0 THEN

SET v_Result = CONCAT(v_Result, ‘]’);

END
IF;

IF SUBSTR(
v_Result,
LENGTH(v_Result) - 1
) = ‘[]’ THEN

SET v_Result = SUBSTR(
v_Result,
1,
LENGTH(v_Result) - 2
);

END
IF;

END
IF;

RETURN v_Result;

END；
/
 
索引
 
普通索引，唯一索引，全文索引都支持，但是不支持bitmap索引。
 
其他：
 
Oracle用||连接字符串，mysql不支持||，但可以用CONCAT来连接，nvl在mysql中是ifnull，instr函数oracle与mysql有些不同，oracle支持的参数比mysql多，功能更强大，但是mysql有substring_index配合substring，length可以做绝大多数的字符的操作
为了支持中文，字符集择utf8，为了支持事务和行级锁选择存储引擎为InnoDB
这些可以在mysql的配置文件里去改，也可以在建表的时候写进sql中
 
 
最后mysql与oracle的语法（临时表，表的comment，定义变量啊等等）函数上的区别还有很多。

oracle转mysql总结
ares-sdk初始开发测试使用的是oracle数据库，由于宁波通商的特殊需要，必须把数据库环境从oracle转向mysql。 现对转换过程中出现的问题及经验总结如下：
主键生成策略
创建一个专门记录序列的表sequence,记录有当前序列号,序列的间隔如+1
创建记录当前序列的表
D
DROP TABLE IF EXISTS sequence;
C
CREATE TABLE sequence (
n
name VARCHAR(50) NOT NULL,
c
current_value INT NOT NULL,
i
increment INT NOT NULL DEFAULT 1,
P
PRIMARY KEY (name)
)
) ENGINE=InnoDB;
I
INSERT INTO sequence VALUES (‘MovieSeq’,3,5);

创建一个获取当前序列的function
D
DROP FUNCTION IF EXISTS currval;
C
CREATE FUNCTION currval (seq_name VARCHAR(50))
R
RETURNS INTEGER
C
CONTAINS SQL
B
BEGIN

DECLARE value INTEGER;

SET value = 0;

SELECT current_value INTO value

FROM sequence

WHERE name = seq_name;

RETURN value;
E
END;

获取下一个数值..先在sequence里面调用update当前最大数值+1然后再调用currval获得当前数值
D
DROP FUNCTION IF EXISTS nextval;
D
DELIMITER $
C
CREATE FUNCTION nextval (seq_name VARCHAR(50))
R
RETURNS INTEGER
C
CONTAINS SQL
B
BEGIN

UPDATE sequence

SET current_value = current_value + increment

WHERE name = seq_name;

RETURN currval(seq_name);
E
END$
D
DELIMITER ;

DROP FUNCTION IF EXISTS setval;
D
DELIMITER $
C
CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER)
R
RETURNS INTEGER
C
CONTAINS SQL
B
BEGIN

UPDATE sequence

SET current_value = value

WHERE name = seq_name;

RETURN currval(seq_name);
E
END$
D
DELIMITER ;

如果以上语句执行异常请先执行这句：set global logbintrustfunctioncreators=TRUE;
插入时的主键生成：
mysql: SELECT MMC.NEXTVAL(‘SEQ_MD_ENTITY_ATTRIBUTE’)
o
oracle: select MMC.SEQ_MD_ENTITY_ATTRIBUTE.nextval from dual

日期处理
mysql : DATE_FORMAT(NOW(),’%Y-%m-%d %H:%i:%s’)
o
oracle: TO_CHAR(SYSDATE,’YYYY-MM-DD hh24:mi:ss’)

nvl函数
mysql: ifnull(A.USER_KPI,0)
o
oracle: NVL(A.USER_KPI,0)

to_number
oracle 的 to_number
m
mysql不需要

关键字、保留字
涉及到关键字,mysql关键字需要加上`号
m
mysql: PARA_VALUE asKEY`
o
oracle : PARA_VALUE as KEY

rownum
Unknown column ‘rownum’ in ‘where clause’
o
oracle自定义sql中如果使用了rownum=1 mysql中可以写成limit 1

大小写问题
在oracle中一般情况下不区分大小写
�
但在MySQL中，所使用操作系统的大小写敏感性决定了数据库名和表名的大小写敏感性。
�
解决的办法是把mysql的数据库名和oracle的大小写保持一致，
�
表名与应用程序中sql字符串中的表名保持一致，
�
如果应用程序中字段名用了双引号，那请把sql中的字段名大小写与双引号里的字符保持一致。
�
如果你的应用程序所引用的表名、字段没统一大小写，那麻烦就大了。

字符串截取
mysql:
�
截取log_data从逗号开始之后的字符：
S
SELECT substring_index(log_data,’,’,-1)

FROM nbts.log where event_id=’150002’ and log_id =’a2a421734c7e47dd8a8b’;

�
截取log_data从逗号开始之前的字符：
S
SELECT substring_index(log_data,’,’,1)

FROM nbts.log where event_id=’150002’ and log_id =’a2a421734c7e47dd8a8b’;

o
oracle :
�
截取log_data从逗号开始之后的字符：
S
SELECT SUBSTR(log_data, INSTR(log_data, ‘,’, 1, 1) +1) AS app_ver_id

FROM nbts.log where event_id=’150002’ and log_id =’a2a421734c7e47dd8a8b’;

�
截取log_data从逗号开始之前的字符：
S
SELECT SUBSTR(log_data,0,INSTR(log_data, ‘,’, 1, 1) - 1) AS app_ver_id

FROM nbts.log where event_id=’150002’ and log_id =’a2a421734c7e47dd8a8b’;

字符串格式化
mysql:CONCAT
o
oracle:TO_CHAR

主键长度问题
在迁移到mysql后可能会出现主键长度太短，需要增加长度。

 
oracle 迁移至 mysql 部分语句的转换
2014-02-26 16:16 4516人阅读 评论(0) 收藏 举报
版权声明：本文为博主原创文章，未经博主允许不得转载。
前几天吧系统从oracle往mysql上迁移，很多的语句是比较简单的，就是一些函数的修改如to-date等
但是也几个比较棘手的，这里记录下

第一、row_number() over(partition by   
首先要了解下oracle中这个函数的用法，看个例子
select t.*,row_number() over(partition by t.owner order by y.createDate desc) rn from test t
这个语句的意思就是，把test这个表的数据按照owner 分组并且给每个分组的里面的数据加上一个序列号，数据格式如下

id       name      owner   createDate  rn
1         aa            001                              1
2          bb           001                              2
3          cc            001                             3
4          dd            002                             1
5           ee           002                              2
6            ff            003                               1
数据搞得不太正规，但是应该能够看懂它的意思吧，

但是在mysql中是没有这个函数的于是乎，找啊找，总算找了个解决方法，如下
SELECT
    heyf_tmp.*,
    IF(@pdept=heyf_tmp.owner ,@rn:=@rn+1,@rn:=1) AS rn,
    @pdept:=heyf_tmp.owner 
FROM
    (
        SELECT
            yv.*
        FROM
            test yv
        ORDER BY
            yv.owner  ,
            yv.createDate  DESC
    )
    heyf_tmp ,
    (
        SELECT
            @rn :=0 ,
            @pdept := NULL ,
            @rn:=0
    )
    aa
具体是什么意思，不是很清楚，不过先解决问题再说

第二、oracle树形查询

oracle树形查询现成的方法
select distinct t.id as id, t.name 
from test t
                start with id=‘’
               connect by prior id = parentid
但是mysql中是没有这个方法的，于是只能自己定义函数或者过程，我这里用的是过程
如下
CREATE PROCEDURE Pro_GetTreeList(in pid varchar(36))
begin 
   declare lev int;
   set lev=1;
   drop table if exists tmp1;    
   CREATE TABLE tmp1(id VARCHAR(40),name varchar(50),parentid varchar(40) ,levv INT);    
   INSERT tmp1 SELECT id,name,parent_id,1 FROMtest` WHERE parent_id=pid;    
  while  row_count()>0 
    do     set lev=lev+1;     
     INSERT tmp1 SELECT t.id,t.name,t.parent_id,lev from testt join tmp1 a on t.parent_id=a.id AND levv=lev-1;–查出子节点
  end while ;    
  INSERT tmp1 SELECT id,name,parent_id,0 FROM test WHERE id=pid;   –查出当前节点
end
这个存储过程应该都能看懂吧，就不做多解释了

第 、函数中定义类似一维、二维数组，

oracle中可以直接定义的，如
return number  is   newList  :=  NewList((1,2,3),(4,5,6));这样可以直接返回   return newList(1,2)    返回的就是2了，
但是mysql中却不能这样写的，不过也有这样的方法解决：
对于一维的来说，很简单，有函数的elt函数，可以直接return   elt(index,’1’,’2’,’3’);   如果elt(3,’1’,’2’,’3’)  这样返回的就是 3了，

不过对于二维的来说好像没现成的函数用了，我的解决方法是改成个表来做，把数据录入到表中，然后去查表，这丫要给你也是可以实现

第四、 mysql中获取汉字的首个大写字母
mysql中遇到了这样的一个问题，网上找到了方法，转载地址：//http://blog.csdn.net/lky5387/article/details/11973721
DROP FUNCTION IF EXISTS getPY;
DELIMITER ;;
CREATE DEFINER=root@% FUNCTION getPY(in_string VARCHAR(65534)) RETURNS mediumtext CHARSET utf8
BEGIN
DECLARE tmp_str VARCHAR(65534) charset gbk DEFAULT ” ; #截取字符串，每次做截取后的字符串存放在该变量中，初始为函数参数in_string值
DECLARE tmp_len SMALLINT DEFAULT 0;#tmp_str的长度
DECLARE tmp_char VARCHAR(2) charset gbk DEFAULT ”;#截取字符，每次 left(tmp_str,1) 返回值存放在该变量中
DECLARE tmp_rs VARCHAR(65534) charset gbk DEFAULT ”;#结果字符串
DECLARE tmp_cc VARCHAR(2) charset gbk DEFAULT ”;#拼音字符，存放单个汉字对应的拼音首字符
SET tmp_str = in_string;#初始化，将in_string赋给tmp_str
SET tmp_len = LENGTH(tmp_str);#初始化长度
WHILE tmp_len > 0 DO #如果被计算的tmp_str长度大于0则进入该while
SET tmp_char = LEFT(tmp_str,1);#获取tmp_str最左端的首个字符，注意这里是获取首个字符，该字符可能是汉字，也可能不是。
SET tmp_cc = tmp_char;#左端首个字符赋值给拼音字符
IF LENGTH(tmp_char)>1 THEN#判断左端首个字符是多字节还是单字节字符，要是多字节则认为是汉字且作以下拼音获取，要是单字节则不处理。
SELECT ELT(INTERVAL(CONV(HEX(tmp_char),16,10),0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,0xBFA6,0xC0AC
,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,0xC8F6,0xCBFA,0xCDDA ,0xCEF4,0xD1B9,0xD4D1),
‘A’,’B’,’C’,’D’,’E’,’F’,’G’,’H’,’J’,’K’,’L’,’M’,’N’,’O’,’P’,’Q’,’R’,’S’,’T’,’W’,’X’,’Y’,’Z’) INTO tmp_cc; #获得汉字拼音首字符
END IF;
SET tmp_rs = CONCAT(tmp_rs,tmp_cc);#将当前tmp_str左端首个字符拼音首字符与返回字符串拼接
SET tmp_str = SUBSTRING(tmp_str,2);#将tmp_str左端首字符去除
SET tmp_len = LENGTH(tmp_str);#计算当前字符串长度
END WHILE;
RETURN tmp_rs;#返回结果字符串
END;;
DELIMITER ;
第四、 图片clob的修改
系统中的功能是把图片存在blob字段中的，，原来用的是oracle，java中对应的字段是Object，但是mysql中如果这样做却不能独处图片，原因可能是因为在往Object中写数据时出错了，这个号解决，只要把java中对应字段的类型改成byte[]  就行了

第五、mysql中 group_concat  与 find_in_set  用法
在项目中遇到这样的需求，
表event中如下字段                                           表work中如下字段
         id                 ename                                              id            wname              eventid
      1001       事件1                                   1          工作1         1001
      1002       事件2                                   2          工作2         1001，1002
      1003       事件3                                   3          工作3         1001,1003
       现查询了一张work表数据，需要展示id   name   event名称，只用一个sql展示，
       select wname,  (select group_concat(ename) from event where find_in_set(id,eventid) ) as ename  from  work;
这一个sql就可以搞定，find_in_set  用法以逗号分隔开与id进行匹配，返回多条记录，  group_concat为把展示的多行记录做成一列展示并以逗号拼接   
 

PKG_COMMON.FN_GETOID()

CREATE TABLE ictms_reservel_order_if_h (
WAREKY varchar(10) NOT NULL COMMENT ‘WH_CD’,
OWNRKY varchar(10) NOT NULL COMMENT ‘Company_CD’,
STDLNR varchar(30) NOT NULL COMMENT ‘BATCH ID’,
PTRCVR varchar(20) NOT NULL COMMENT ‘CUSTOMER_CD’,
DNAME3 varchar(180) NOT NULL COMMENT ‘CUSTOMER Name’,
PGRC02 varchar(20) NOT NULL COMMENT ‘Allocation Unit’,
SKUKEY varchar(20) NOT NULL COMMENT ‘ITEM CD’,
SEQNO NUMERIC(10) NOT NULL COMMENT ‘SEQ’,
DESC01 varchar(120) NOT NULL COMMENT ‘ITEM Name’,
QTSHPO NUMERIC(20,3) NOT NULL COMMENT ‘Quantity’,
PROC_STATUS varchar(2) NOT NULL COMMENT ‘Status ( 10 -> start , 20 : cancel )’,
IFFLG varchar(1) NOT NULL COMMENT ‘IF Flag’,
ERTXT varchar(1000) NOT NULL COMMENT ‘Error Message’,
CREDAT varchar(8) NOT NULL COMMENT ‘Creation date’,
CRETIM varchar(6) NOT NULL COMMENT ‘Creation time’,
CREUSR varchar(20) NOT NULL COMMENT ‘Created by’,
LMODAT varchar(8) NOT NULL COMMENT ‘Last modified date’,
LMOTIM varchar(6) NOT NULL COMMENT ‘Last modified time’,
LMOUSR varchar(20) NOT NULL COMMENT ‘Last modified by’,
INDBZL varchar(1) NOT NULL COMMENT ‘INDBZL’,
INDARC varchar(1) NOT NULL COMMENT ‘INDARC’,
UPDCHK NUMERIC(5) NOT NULL COMMENT ‘UPDCHK’

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE ictms_custom_if_h
ADD COLUMN CREDAT varchar(8) CHARACTER SET utf8 NULL DEFAULT ‘now()’ COMMENT ‘create date’;

UPDATE ictms_reservel_order_if_h T
SET CREDAT = CONCAT(SUBSTR(T.CREDAT,1,4),SUBSTR(T.CREDAT,6,2),SUBSTR(T.CREDAT,9,2)),
T.LMODAT = CONCAT(SUBSTR(T.LMODAT,1,4),SUBSTR(T.LMODAT,6,2),SUBSTR(T.LMODAT,9,2)),
T.CRETIM = CONCAT(SUBSTR(T.CRETIM,1,2),SUBSTR(T.CRETIM,4,2),SUBSTR(T.CRETIM,7,2)),
T.LMOTIM = CONCAT(SUBSTR(T.LMOTIM,1,2),SUBSTR(T.LMOTIM,4,2),SUBSTR(T.LMOTIM,7,2));

Mysql存储过程中使用cursor
2014-06-08 15:29 10881人阅读 评论(0) 收藏 举报
 分类:
【数据库】综合(105) 
作者同类文章X
版权声明:本文为博主原创文章，未经博主允许不得转载。
一、表
学生表
CREATE TABLE t_student (
   stuNum int(11) NOT NULL auto_increment,
   stuName varchar(20) default NULL,
   birthday date default NULL,
   PRIMARY KEY  (stuNum)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

学生分数表
CREATE TABLE t_stu_score (
   id int(11) NOT NULL auto_increment,
   stuNum int(11) default NULL,
   score decimal(6,2) default NULL,
   PRIMARY KEY  (id),
   KEY FK_t_stu_score (stuNum),
   CONSTRAINT FK_t_stu_score FOREIGN KEY (stuNum) REFERENCES t_student (stuNum)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

学生详细表
CREATE TABLE t_stu_detail (
   id int(11) NOT NULL auto_increment,
   stuName varchar(20) default NULL,
   score decimal(6,2) default NULL,
   PRIMARY KEY  (id)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

将t_Student和t_stu_score表中满足一定条件的数据插入到t_stu_detail中。

二、过程
DELIMITER &&
 CREATE PROCEDURE proc_AddStuDetail( IN p_score DECIMAL(6,2) )
  BEGIN
DECLARE vstuNum INT;
DECLARE vstuName VARCHAR(20);
DECLARE vbirthday DATE;
DECLARE vscore DECIMAL(6,2);
DECLARE done INT;

– 定义游标
DECLARE stuCursor CURSOR
FOR
SELECT stuNum,stuName,birthday FROM t_Student;

– 定义结束标记
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

– 打开游标
OPEN stuCursor;

– 循环
stuLoop:LOOP
– 取游标中的数据
FETCH stuCursor INTO vstuNum,vstuName,vbirthday;
IF done = 1 THEN
LEAVE stuLoop;
END IF;

IF DATE(vbirthday) >= ‘1990-03-01’ THEN
SELECT score INTO vscore FROM t_stu_score WHERE stuNum = vstuNum;
IF vscore >= p_score THEN
INSERT INTO t_stu_detail VALUES(NULL,vstuNum,vscore);
END IF;  
END IF;
END LOOP stuLoop;

– 关闭游标
CLOSE stuCursor;
  END
&&
DELIMITER ;

、调用过程
CALL proc_AddStuDetail(86);

drop procedure if exists pro_rep_shadow_rs;

delimiter |

– rep_shadow_rs
– 用来处理信息的增加，更新和删除
– 每次只更新上次以来没做过的数据
– 根据不同的标志位
– 需要一个输出的参数，
– 如果返回为0，则调用失败，事务回滚

– 如果返回为1，调用成功，事务提交

– 测试方法
– call pro_rep_shadow_rs(@rtn);

– select @rtn;

create procedure pro_rep_shadow_rs(out rtn int)
begin
– 声明变量，所的声明必须在非声明的语句前面
declare iLast_rep_sync_id int default -1;
declare iMax_rep_sync_id int default -1;
– 如果出现异常，或自动处理并rollback,但不再通知调用方了
– 如果希望应用获得异常，需要将下面这一句，以及启动事务和提交事务的语句全部去掉
declare exit handler for sqlexception rollback;
– 查找上一次的
select eid into iLast_rep_sync_id from rep_de_proc_log where tbl=’rep_shadow_rs’;
– 如果不存在，则增加一行
if iLast_rep_sync_id=-1 then
insert into rep_de_proc_log(rid,eid,tbl) values(0,0,’rep_shadow_rs’);
set iLast_rep_sync_id = 0;
end if;

-- 下一个数字  
set iLast_rep_sync_id=iLast_rep_sync_id+1;  
-- 设置默认的返回值为0：失败  
set rtn=0;  

-- 启动事务  
start transaction;  
-- 查找最大编号  
select max(rep_sync_id) into iMax_rep_sync_id from rep_shadow_rs;  
-- 新数据  
if iMax_rep_sync_id>=iLast_rep_sync_id then  
    -- 调用  
    call pro_rep_shadow_rs_do(iLast_rep_sync_id,iMax_rep_sync_id);  
    -- 更新日志  
    update rep_de_proc_log set rid=iLast_rep_sync_id,eid=iMax_rep_sync_id where tbl='rep_shadow_rs';  
end if;  

-- 运行没异常，提交事务  
commit;  
-- 设置返回值为1
set rtn=1;  

end;
|
delimiter ;
drop procedure if exists pro_rep_shadow_rs_do;

delimiter |

– 处理指定编号范围内的数据
– 需要输入2个参数
– last_rep_sync_id 是编号的最小值
– max_rep_sync_id 是编号的最大值

– 无返回值

create procedure pro_rep_shadow_rs_do(last_rep_sync_id int, max_rep_sync_id int)
begin
declare iRep_operationtype varchar(1);
declare iRep_status varchar(1);
declare iRep_Sync_id int;
declare iId int;
– 这个用于处理游标到达最后一行的情况
declare stop int default 0;
– 声明游标
declare cur cursor for select id,Rep_operationtype,iRep_status,rep_sync_id from rep_shadow_rs where rep_sync_id between last_rep_sync_id and max_rep_sync_id;
– 声明游标的异常处理，设置一个终止标记
declare CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET stop=1;

-- 打开游标  
open cur;  

-- 读取一行数据到变量  
fetch cur into iId,iRep_operationtype,iRep_status,iRep_Sync_id;  
-- 这个就是判断是否游标已经到达了最后  
while stop <> 1 do
    -- 各种判断  
    if iRep_operationtype='I' then  
        insert into rs0811 (id,fnbm) select id,fnbm from rep_shadow_rs where rep_sync_id=iRep_sync_id;  
    elseif iRep_operationtype='U' then  
    begin  
        if iRep_status='A' then  
            insert into rs0811 (id,fnbm) select id,fnbm from rep_shadow_rs where rep_sync_id=iRep_sync_id;  
        elseif iRep_status='B' then  
            delete from rs0811 where id=iId;  
        end if;  
    end;  
    elseif iRep_operationtype='D' then  
        delete from rs0811 where id=iId;  
    end if;   

    -- 读取下一行的数据   
    fetch cur into iId,iRep_operationtype,iRep_status,iRep_Sync_id;  
end while; -- 循环结束  
close cur; -- 关闭游标  

end;
use testprocedure;
delimiter //

create procedure simpleproce1 (out par1 int)
begin
select count(*) into par1 from proce;
end
//
delimiter ;
call simpleproce1(@a);
select @a;

<2>,每次只单一的行可以被取回select id,name into par1,par2 from proce LIMIT 1;中的LIMIT 1;

use testprocedure;
delimiter //
DROP procedure IF EXISTS simpleproce2
create procedure simpleproce2 (out par1 int,out par2 char(30))
begin
select id,name into par1,par2 from proce LIMIT 1;
end
//
delimiter ;
call simpleproce2(@a,@b);
select @a,@b;

****second test,function*******

<3>

delimiter //
DROP FUNCTION IF EXISTS hello
//
create function hello(s char(20)) returns char(50)
return concat(‘Hello, ‘,s,’!’);
//
delimiter ;
select hello(‘world’);
show create function testprocedure.helloG

它返回子程序的特征，如数据库，名字，类型，创建者及创建和修改日期

show function status like ‘hello’G

<4>

注意name不能和字段名相同

delimiter //
DROP procedure IF EXISTS test //
CREATE PROCEDURE test ()
BEGIN
DECLARE name VARCHAR(5) DEFAULT ‘bob’;
DECLARE newname VARCHAR(5);
DECLARE xid INT;

SELECT name,id INTO newname,xid
  FROM proce WHERE name = name;
SELECT newname;

END;
//
call test1() //

*

delimiter //
DROP procedure IF EXISTS test2 //
CREATE PROCEDURE test2 ()
BEGIN

DECLARE newname VARCHAR(5);
DECLARE xid INT;

SELECT name,id INTO newname,xid
  FROM proce limit 1;
SELECT newname,xid;

END;
//
call test2() //

<5>

use testprocedure;
CREATE PROCEDURE p1 () SELECT * FROM proce;
call p1();

<6>注意此处的handler是设置SQLSTATE值，SQLWARNING是对所以01开头的SQLSTATE代码的速记

NOT FOUND是对所以02开头的SQLSTATE代码的速记

SQLEXCEPTION是对所没被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记

DECLARE CONTINUE HANDLER声明CONTINUE异常处理

事实上这里的23000SQLSTATE是更常用的，当外键约束出错或主键约束出错就被调用了。

当没发生该23000异常时， select @x2的值将是null,而不是1,

并且后面的第2个语句执行时将会报主键约束错误，此时@x2=1,@x=4,虽然第2句了异常，但是后面的语句继续执行

保存到数据的数据是3,test3和5,test5

use testprocedure;
delimiter //
DROP procedure IF EXISTS handlerdemo
//
create procedure handlerdemo()
begin
declare continue handler for sqlstate ‘23000’ set @x2=1;
set @x=1;
insert into proce values(3,’test3’);
set @x=2;
insert into proce values(3,’test4’);
set @x=3;
insert into proce values(5,’test5’);
set @x=4;
end;
//
call handlerdemo()//
select @x //
select @x2 //

**光标******

<7>光标必须在声明处理程序之前被声明，并且变量和条件必须在声明光标或处理程序之前被声明

在这里先声明变量a,b,c,后声明cursor

create procedure curdemo()
begin
declare done int default 0;
declare a char(16);
declare b,c int;
declare cur1 cursor for select id,name from proce;
declare cur2 cursor for select id from proce2;
declare continue handler for sqlstate ‘02000’ set done=1;
open cur1;
open cur2;
repeat
fetch cur1 into b,a;
fetch cur2 into c;
if not done then
if b

****** Case *********

<8>when … then ;case … end case;

delimiter //
DROP procedure IF EXISTS p13
//
create procedure p13(in par1 int)
begin
declare var1 int;
set var1=par1+1;
case var1
when 0 then insert into casetest values(17);
when 1 then insert into casetest values(18);
else insert into casetest values(19);
end case;
end;
//
call p13(-1)//
call p13(0)//
call p13(1)//
call p13(null)//

****** while ******

<9>while … do … end while;为了防止null的错误，set v=0是必须的

delimiter //
DROP procedure IF EXISTS p14
//
create procedure p14()
begin
declare v int;
set v=0;
while v < 5 do
insert into casetest values (v);
set v=v+1;
end while;
end;//
call p14()//

******* repeat *******

<10>repeat …until … end repeat; 是执行后检查（until v>=5，而while是执行前检查(while v<5)

delimiter //
DROP procedure IF EXISTS p15
//
create procedure p15()
begin
declare v int;
set v=0;
repeat
insert into casetest values(v);
set v=v+1;
until v >=5
end repeat;
end;
//
call p15()//

******* loops *******

<11> loop 和while一样不需要初始条件，同时和repeat一样不需要结束条件

loop_label: loop

…

if .. then

leave loop_label

end if

end loop

delimiter //
DROP procedure IF EXISTS p16
//
create procedure p16()
begin
declare v int;
set v=0;
loop_label: loop
insert into casetest values(v);
set v=v+1;
if v >=5 then
leave loop_label;
end if;
end loop;
end;//
call p16()//

******* Labels *******

<12>labels标号； 注意此处的until 0=0后面没分号“;”

delimiter //
DROP procedure IF EXISTS p17//
create procedure p17()
label_1:begin
label_2:while 0=1 do leave label_2; end while;
label_3:repeat leave label_3;until 0=0 end repeat;
label_4:loop leave label_4; end loop;
end;//
call p17()//

<13>labels 标号结束符；

delimiter //
DROP procedure IF EXISTS p18//
create procedure p18()
label_1:begin
label_2:while 0=1 do leave label_2; end while label_2;
label_3:repeat leave label_3;until 0=0 end repeat label_3;
label_4:loop leave label_4; end loop label_4;
end label_1;//
call p18()//

<14>leave和labels 跳出和标号;leave 使程序跳出复杂的语句

delimiter //
DROP procedure IF EXISTS p19//
create procedure p19(par char)
label_1:begin
label_2:begin
label_3:begin
if par is not null then
if par=’a’ then leave label_1;
else
begin
if par=’b’ then
leave label_2;
else
leave label_3;
end if;
end;
end if;
end if;
end label_3;
end label_2;
end label_1;
//
call p19(‘a’)//

<15>iterate迭代，必须用leave;iterate意思是重新开始复合语句，相当于 continue

该结果中3将不被保存到数据库表中

delimiter //
DROP procedure IF EXISTS p20//
create procedure p20()
begin
declare v int;
set v=0;
loop_label:loop
if v=3 then
set v=v+1;
iterate loop_label;
end if;
insert into casetest values(v);
set v=v+1;
if v>=5 then
leave loop_label;
end if;
end loop loop_label;
end;//
call p20()//

<16>Grand combination大组合

delimiter //
DROP procedure IF EXISTS p21//
create procedure p21(in par1 int,out par2 int)
language sql deterministic sql security invoker
begin
declare v int;
label goto_label;
start_label:loop
if v=v then
leave start_label;
else
iterate start_label;
end if;
end loop start_label;
repeat
while 1=0 do begin end;
end while;
until v=v
end repeat;
goto goto_label;

end;
//
call p21()//

****** trigger *****************

<17>

use testprocedure;
CREATE TABLE trig1(a1 int);
CREATE TABLE trig2(a2 int);
CREATE TABLE trig3(a3 int not null AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE trig4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);
insert into trig3(a3) values(null),(null),(null),(null),(null),(null),(null),(null),(null),(null);
insert into trig4(a4) values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);
delimiter //
DROP trigger trigtest//
create trigger trigtest before insert on trig1
for each row begin
insert into trig2 set a2=NEW.a1;
delete from trig3 where a3=NEW.a1;
update trig4 set b4=b4+1 where a4=NEW.a1;
end;
//

delimiter ;
INSERT INTO trig1 VALUES(1), (3), (1), (7), (1), (8), (4), (4);

---

Drop Procedure If Exists p_report;
Delimiter forend
Create Procedure p_report
(In year Int,
In month Int,
In Id Char(10),
Out status Int)
Begin
SelectYear,
Month,
OfficeId,
OnTimeRate
FromReportByMonth
WhereOfficeId = Id And
Year = year;
End
forend
Delimiter ;

Oracle中的decode在mysql中的等价实现
[日期：2010-10-22]
来源：CentOS社区  作者：kaisep
[字体：大 中 小]

mysql支持if 
格式：
IF(expr1,expr2,expr3)
如果expr1是TRUE(expr1<>;0且expr1<>;NULL)，那么IF()返回expr2，否则它返回expr3。IF()返回一个数字或字符串值
例如
SELECT IF(1<2, ‘yes ‘, ‘no ‘); 
输出yes
同时，还支持case 
SELECT   CASE   WHEN   1> 0   THEN   “yes ”   ELSE   “no ”   END; 
还是输出yes
来例子说明：
例一
//Oracle中 decode(Emergency,1,’紧急’,’普通’)
//mysql
select a.title,if(a.Emergency=1,’紧急’,’普通’)emergency from already_sign a
Select title,case Emergency when 1 then ‘紧急’ else ‘普通’ End as emergency   from already_sign

例二
oracle的写法
SELECT decode(ttype,1,’a’,2,’b’,3,’c’,’d’) FROM taba
可以在mysql里写成
SELECT if(ttype=1, ‘a’,if(ttype=2,’b’, if(ttype =3, ‘c’, ‘d’))) FROM taba
同理
DECODE(hj_bz, 1, ‘总库入’, 2, ‘总库出’,3,’分库出’,4,’已开票’,5,’已安装’,6,’已收款’)
if(hj_bz=1,’总库入’,if(hj_bz=2,’总库出’,if(hj_bz=3,’分库出’,if(hj_bz=4,’已开票’,if(hj_bz=5,’已安装’,’已收款’)))))

===============================================================================================

MySQL服务器进程CPU占用100%的解决方法
转载  2010-12-30   作者：   我要评论
早上帮朋友一台服务器解决了 Mysql cpu 占用 100% 的问题。稍整理了一下，将经验记录在这篇文章里。

朋友主机(Windows 2003 + IIS + PHP + MYSQL )近来 MySQL 服务进程 (mysqld-nt.exe) CPU 占用率总为 100% 高居不下。此主机有10个左右的 database, 分别给十个网站调用。据朋友测试，导致 mysqld-nt.exe cpu 占用奇高的是网站A，一旦在 IIS 中将此网站停止服务，CPU 占用就降下来了。一启用，则马上上升。

　MYSQL CPU 占用 100% 的解决过程
今天早上仔细检查了一下。目前此网站的七日平均日 IP 为2000，PageView 为 3万左右。网站A 用的 database 目前有39个表，记录数 60.1万条，占空间 45MB。按这个数据，MySQL 不可能占用这么高的资源。
　　于是在服务器上运行命令，将 mysql 当前的环境变量输出到文件 output.txt：
d:\web\mysql> mysqld.exe –help >output.txt
　　发现 tmp_table_size 的值是默认的 32M，于是修改 My.ini, 将 tmp_table_size 赋值到 200M:
d:\web\mysql> notepad c:\windows\my.ini
[
[mysqld]
t
tmp_table_size=200M

　　然后重启 MySQL 服务。CPU 占用有轻微下降，以前的CPU 占用波形图是 100% 一根直线，现在则在 97%~100%之间起伏。这表明调整 tmp_table_size 参数对 MYSQL 性能提升有改善作用。但问题还没完全解决。
　　于是进入 mysql 的 shell 命令行，调用 show processlist, 查看当前 mysql 使用频繁的 sql 语句：
mysql> show processlist;
　　反复调用此命令，发现网站 A 的两个 SQL 语句经常在 process list 中出现，其语法如下：
SELECT t1.pid, t2.userid, t3.count, t1.date
F
FROM _mydata AS t1
L
LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid
L
LEFT JOIN _mydata_body AS t2 ON t1.pid=t3.pid
O
ORDER BY t1.pid
L
LIMIT 0,15
　　调用 show columns 检查这三个表的结构 :
mysql> show columns from _myuser;
m
mysql> show columns from _mydata;
m
mysql> show columns from _mydata_body;
　　终于发现了问题所在：_mydata 表，只根据 pid 建立了一个 primary key，但并没有为 userid 建立索引。而在这个 SQL 语句的第一个 LEFT JOIN ON 子句中：
LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid
　　_mydata 的 userid 被参与了条件比较运算。于是我为给 _mydata 表根据字段 userid 建立了一个索引：
mysql> ALTER TABLE _mydata ADD INDEX ( userid )
　　建立此索引之后，CPU 马上降到了 80% 左右。看到找到了问题所在，于是检查另一个反复出现在 show processlist 中的 sql 语句：
SELECT COUNT(*)
F
FROM _mydata AS t1, _mydata_key AS t2
W
WHERE t1.pid=t2.pid and t2.keywords = ‘孔雀’
　　经检查 _mydata_key 表的结构，发现它只为 pid 建了了 primary key, 没有为 keywords 建立 index。_mydata_key 目前有 33 万条记录，在没有索引的情况下对33万条记录进行文本检索匹配，不耗费大量的 cpu 时间才怪。看来就是针对这个表的检索出问题了。于是同样为 _mydata_key 表根据字段 keywords 加上索引:
mysql> ALTER TABLE _mydata_key ADD INDEX ( keywords )
　　建立此索引之后，CPU立刻降了下来，在 50%~70%之间震荡。
　　再次调用 show prosslist，网站A 的sql 调用就很少出现在结果列表中了。但发现此主机运行了几个 Discuz 的论坛程序， Discuz 论坛的好几个表也存在着这个问题。于是顺手一并解决，cpu占用再次降下来了。(2007.07.09 附注：关于 discuz 论坛的具体优化过程，我后来另写了一篇文章，详见：千万级记录的 Discuz! 论坛导致 MySQL CPU 100% 的 优化笔记 http://www.xiaohui.com/dev/server/20070701-discuz-mysql-cpu-100-optimize.htm)
解决 MYSQL CPU 占用 100% 的经验总结
增加 tmp_table_size 值。mysql 的配置文件中，tmp_table_size 的默认大小是 32M。如果一张临时表超出该大小，MySQL产生一个 The table tbl_name is full 形式的错误，如果你做很多高级 GROUP BY 查询，增加 tmp_table_size 值。 这是 mysql 官方关于此选项的解释：
tmp_table_size
This variable determines the maximum size for a temporary table in memory. If the table becomes too large, a MYISAM table is created on disk. Try to avoid temporary tables by optimizing the queries where possible, but where this is not possible, try to ensure temporary tables are always stored in memory. Watching the processlist for queries with temporary tables that take too long to resolve can give you an early warning that tmp_table_size needs to be upped. Be aware that memory is also allocated per-thread. An example where upping this worked for more was a server where I upped this from 32MB (the default) to 64MB with immediate effect. The quicker resolution of queries resulted in less threads being active at any one time, with all-round benefits for the server, and available memory.
对 WHERE, JOIN, MAX(), MIN(), ORDER BY 等子句中的条件判断中用到的字段,应该根据其建立索引 INDEX。索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大，花费时间越多。如果表对于查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要考虑所有数据。如果一个表有1000行，这比顺序读取至少快100倍。所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B树中存储。
根据 mysql 的开发文档:
索引 index 用于：
快速找出匹配一个WHERE子句的行
当执行联结(JOIN)时，从其他表检索行。
对特定的索引列找出MAX()或MIN()值
如果排序或分组在一个可用键的最左面前缀上进行(例如，ORDER BY key_part_1,key_part_2)，排序或分组一个表。如果所有键值部分跟随DESC，键以倒序被读取。
在一些情况中，一个查询能被优化来检索值，不用咨询数据文件。如果对某些表的所有使用的列是数字型的并且构成某些键的最左面前缀，为了更快，值可以从索引树被检索出来。

假定你发出下列SELECT语句：
mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;如果一个多列索引存在于col1和col2上，适当的行可以直接被取出。如果分开的单行列索引存在于col1和col2上，优化器试图通过决定哪个索引将找到更少的行并来找出更具限制性的索引并且使用该索引取行。
开发人员做 SQL 数据表设计的时候，一定要通盘考虑清楚。

=================================================================================================================
MySql取得日期（前一天、某一天）
 (2013-07-29 21:59:28)
转载▼
标签： 
it
分类： 工作日志

取得当天：
SELECT curdate();
mysql> SELECT curdate();
+————+
| curdate()  |
+————+
| 2013-07-29 |
+————+
 
取得当前日期：
mysql> select now();
+———————+
| now()               |
+———————+
| 2013-07-29 22:10:40 |
+———————+
取得前一天：
mysql> select date_sub(curdate(),interval 1 day);
+————————————+
| date_sub(curdate(),interval 1 day) |
+————————————+
| 2013-07-28                         |
+————————————+
 
括号中为当天时间的前一天，如果统计前几天就将括号中的’1’改成相应的天数。如果要算月或年，直接将day改为month或year即可
 
 取得前一天的年份：
mysql> SELECT YEAR(DATE_SUB(CURDATE(),INTERVAL 1 DAY));
+——————————————+
| YEAR(DATE_SUB(CURDATE(),INTERVAL 1 DAY)) |
+——————————————+
|                                     2013 |
+——————————————+
 
 
 
 date_sub()函数的例子：
今天是2013年5月20日。
date_sub(‘2012-05-25’,interval 1 day) 表示 2012-05-24
date_sub(‘2012-05-25’,interval 0 day) 表示 2012-05-25
date_sub(‘2012-05-25’,interval -1 day) 表示 2012-05-26
date_sub(‘2012-05-31’,interval -1 day) 表示 2012-06-01
date_sub(curdate(),interval 1 day) 表示 2013-05-19
date_sub(curdate(),interval -1 day) 表示 2013-05-21
date_sub(curdate(),interval 1 month) 表示 2013-04-20
date_sub(curdate(),interval -1 month) 表示 2013-06-20
date_sub(curdate(),interval 1 year) 表示 2012-05-20
date_sub(curdate(),interval -1 year) 表示 2014-05-20

========================================================================================

mysql 行转列和列转行实例详解
这篇文章主要介绍了mysql 行转列和列转行实例详解的相关资料,需要的朋友可以参考下
mysql行转列、列转行
 语句不难，不做多余解释了，看语句时，从内往外一句一句剖析
行转列
       如图所示的表，现在希望查询的结果将行转成列

       建表语句如下：
?
1
2
3
4
5
6
7
CREATE TABLE TEST_TB_GRADE (
 ID int(10) NOT NULL AUTO_INCREMENT,
 USER_NAME varchar(20) DEFAULT NULL,
 COURSE varchar(20) DEFAULT NULL,
 SCORE float DEFAULT ‘0’,
 PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

?
1
2
3
4
5
6
7
8
9
10
insert into TEST_TB_GRADE(USER_NAME, COURSE, SCORE) values
(“张”, “数学”, 34),
(“张”, “语文”, 58),
(“张”, “英语”, 58),
(“李四”, “数学”, 45),
(“李四”, “语文”, 87),
(“李四”, “英语”, 45),
(“王五”, “数学”, 76),
(“王五”, “语文”, 34),
(“王五”, “英语”, 89);

       查询语句：
       此处用之所以用MAX是为了将无数据的点设为0，防止出现NULL
?
1
2
3
4
5
6
SELECT user_name ,
  MAX(CASE course WHEN ‘数学’ THEN score ELSE 0 END ) 数学,
  MAX(CASE course WHEN ‘语文’ THEN score ELSE 0 END ) 语文,
  MAX(CASE course WHEN ‘英语’ THEN score ELSE 0 END ) 英语
FROM test_tb_grade
GROUP BY USER_NAME;

       结果展示：

列转行
       如图所示的表，现在希望查询的结果将列成行

       建表语句如下：
?
1
2
3
4
5
6
7
8
CREATE TABLE TEST_TB_GRADE2 (
 ID int(10) NOT NULL AUTO_INCREMENT,
 USER_NAME varchar(20) DEFAULT NULL,
 CN_SCORE float DEFAULT NULL,
 MATH_SCORE float DEFAULT NULL,
 EN_SCORE float DEFAULT ‘0’,
 PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

?
1
2
3
4
insert into TEST_TB_GRADE2(USER_NAME, CN_SCORE, MATH_SCORE, EN_SCORE) values
(“张”, 34, 58, 58),
(“李四”, 45, 87, 45),
(“王五”, 76, 34, 89);

查询语句：
?
1
2
3
4
select user_name, ‘语文’ COURSE , CN_SCORE as SCORE from test_tb_grade2
union select user_name, ‘数学’ COURSE, MATH_SCORE as SCORE from test_tb_grade2
union select user_name, ‘英语’ COURSE, EN_SCORE as SCORE from test_tb_grade2
order by user_name,COURSE;

       结果展示：

感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！

================================
MySQL的行转列、列转行、连接字符串 concat、concat_ws、group_concat函数用法
转载 2014年11月19日 13:47:31
标签：
concat /
group_concat /
mysql行转列列转行 /
concat_ws /
mysql连接字符串
100557
MySQL的行转列、列转行、连接字符串  concat、concat_ws、group_concat函数用法
使用方法：
CONCAT(str1,str2,…)  
返回结果为连接参数产生的字符串。如任何一个参数为NULL ，则返回值为 NULL。
注意：
如果所参数均为非二进制字符串，则结果为非二进制字符串。 
如果自变量中含任一二进制字符串，则结果为一个二进制字符串。
一个数字参数被转化为与之相等的二进制字符串格式；若要避免这种情况，可使用显式类型 cast, 例如：
SELECT CONCAT(CAST(int_col AS CHAR), char_col)
MySQL的concat函数可以连接一个或者多个字符串,如
mysql> select concat(‘10’);
+————–+
| concat(‘10’) |
+————–+
| 10   |
+————–+
1 row in set (0.00 sec)
mysql> select concat(‘11’,’22’,’33’);
+————————+
| concat(‘11’,’22’,’33’) |
+————————+
| 112233 |
+————————+
1 row in set (0.00 sec)
MySQL的concat函数在连接字符串的时候，只要其中一个是NULL,那么将返回NULL
mysql> select concat(‘11’,’22’,null);
+————————+
| concat(‘11’,’22’,null) |
+————————+
| NULL   |
+————————+
1 row in set (0.00 sec)
MySQL中concat_ws函数
使用方法： 
CONCAT_WS(separator,str1,str2,…)
CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。
注意：
如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。
如连接后以逗号分隔 
mysql> select concat_ws(‘,’,’11’,’22’,’33’);
+——————————-+
| concat_ws(‘,’,’11’,’22’,’33’) |
+——————————-+
| 11,22,33 |
+——————————-+
1 row in set (0.00 sec)
和MySQL中concat函数不同的是, concat_ws函数在执行的时候,不会因为NULL值而返回NULL 
mysql> select concat_ws(‘,’,’11’,’22’,NULL);
+——————————-+
| concat_ws(‘,’,’11’,’22’,NULL) |
+——————————-+
| 11,22 |
+——————————-+
1 row in set (0.00 sec)
MySQL中group_concat函数
完整的语法如下：
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator ‘分隔符’])
基本查询
mysql> select * from aa;
+——+——+
| id| name |
+——+——+
|1 | 10|
|1 | 20|
|1 | 20|
|2 | 20|
|3 | 200   |
|3 | 500   |
+——+——+
6 rows in set (0.00 sec)
以id分组，把name字段的值打印在一行，逗号分隔(默认)
mysql> select id,group_concat(name) from aa group by id;
+——+——————–+
| id| group_concat(name) |
+——+——————–+
|1 | 10,20,20|
|2 | 20 |
|3 | 200,500|
+——+——————–+
3 rows in set (0.00 sec)
以id分组，把name字段的值打印在一行，分号分隔
mysql> select id,group_concat(name separator ‘;’) from aa group by id;
+——+———————————-+
| id| group_concat(name separator ‘;’) |
+——+———————————-+
|1 | 10;20;20 |
|2 | 20|
|3 | 200;500   |
+——+———————————-+
3 rows in set (0.00 sec)
以id分组，把去冗余的name字段的值打印在一行，
逗号分隔
mysql> select id,group_concat(distinct name) from aa group by id;
+——+—————————–+
| id| group_concat(distinct name) |
+——+—————————–+
|1 | 10,20|
|2 | 20   |
|3 | 200,500 |
+——+—————————–+
3 rows in set (0.00 sec)
以id分组，把name字段的值打印在一行，逗号分隔，以name排倒序
mysql> select id,group_concat(name order by name desc) from aa group by id;
+——+—————————————+
| id| group_concat(name order by name desc) |
+——+—————————————+
|1 | 20,20,10   |
|2 | 20|
|3 | 500,200|
+——+—————————————+
3 rows in set (0.00 sec)
repeat()函数
    用来复制字符串,如下’ab’表示要复制的字符串，2表示复制的份数
    mysql> select repeat(‘ab’,2);
+—————-+
| repeat(‘ab’,2) |
+—————-+
| abab           |
+—————-+
   1 row in set (0.00 sec)
   又如
mysql> select repeat(‘a’,2);
+—————+
| repeat(‘a’,2) |
+—————+
| aa            |
+—————+
1 row in set (0.00 sec)

===================================================================================================
MYSQL动态行转列
转载 2016年11月15日 10:04:44
2632
DROP TABLE IF EXISTS score;
CREATE TABLE score (
id int(11) NOT NULL AUTO_INCREMENT,
class varchar(255) DEFAULT NULL,
score double DEFAULT NULL,
userid int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

INSERT INTO score VALUES (‘1’, ‘math’, ‘90’, ‘1’);
INSERT INTO score VALUES (‘2’, ‘english’, ‘90’, ‘1’);
INSERT INTO score VALUES (‘3’, ‘computer’, ‘80’, ‘1’);
INSERT INTO score VALUES (‘4’, ‘sports’, ‘90’, ‘1’);
INSERT INTO score VALUES (‘5’, ‘math’, ‘80’, ‘2’);
INSERT INTO score VALUES (‘6’, ‘english’, ‘85’, ‘2’);
INSERT INTO score VALUES (‘7’, ‘computer’, ‘100’, ‘2’);

查询语句
SET @EE=”; 
set @str_tmp=”; 
SELECT @EE:=CONCAT(@EE,’SUM(IF(class=\”,class,’\”,’,score,0)) AS ‘,class,’,’) as aa into @str_tmp FROM (SELECT DISTINCT class FROM score) A order by length(aa) desc limit 1; 
SET @QQ=CONCAT(‘SELECT ifnull(score.userid,\’total\’),’,LEFT(@str_tmp,char_length(@str_tmp)-1),’ ,SUM(score) AS TOTAL FROM score GROUP BY userid WITH ROLLUP’); 
PREPARE stmt FROM @QQ; 
EXECUTE stmt ; 
deallocate prepare stmt;
原来的记录：

新查询出的结果：

=========================================================================================================

mysql 行列动态转换的实现(列联表,交叉表)
转载  2017-01-03   投稿：jingxian   我要评论
下面小编就为大家带来一篇mysql 行列动态转换的实现(列联表,交叉表)。小编觉得挺不错的，现在就分享给大家，也给大家做个参考。一起跟随小编过来看看吧
（1动态，适用于列不确定情况

1
2
3
4
5
6
create table table_name(
 id int primary key,
 col1 char(2),
 col2 char(2),
 col3 int
);

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
insert into table_name values
(1 ,’A1’,’B1’,9),
(2 ,’A2’,’B1’,7),
(3 ,’A3’,’B1’,4),
(4 ,’A4’,’B1’,2),
(5 ,’A1’,’B2’,2),
(6 ,’A2’,’B2’,9),
(7 ,’A3’,’B2’,8),
(8 ,’A4’,’B2’,5),
(9 ,’A1’,’B3’,1),
(10 ,’A2’,’B3’,8),
(11 ,’A3’,’B3’,8),
(12 ,’A4’,’B3’,6),
(13 ,’A1’,’B4’,8),
(14 ,’A2’,’B4’,2),
(15 ,’A3’,’B4’,6),
(16 ,’A4’,’B4’,9),
(17 ,’A1’,’B4’,3),
(18 ,’A2’,’B4’,5),
(19 ,’A3’,’B4’,2),
(20 ,’A4’,’B4’,5);

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
select * from table_name;
+—-+——+——+——+
| id | col1 | col2 | col3 |
+—-+——+——+——+
| 1 | A1  | B1  |  9 |
| 2 | A2  | B1  |  7 |
| 3 | A3  | B1  |  4 |
| 4 | A4  | B1  |  2 |
| 5 | A1  | B2  |  2 |
| 6 | A2  | B2  |  9 |
| 7 | A3  | B2  |  8 |
| 8 | A4  | B2  |  5 |
| 9 | A1  | B3  |  1 |
| 10 | A2  | B3  |  8 |
| 11 | A3  | B3  |  8 |
| 12 | A4  | B3  |  6 |
| 13 | A1  | B4  |  8 |
| 14 | A2  | B4  |  2 |
| 15 | A3  | B4  |  6 |
| 16 | A4  | B4  |  9 |
| 17 | A1  | B4  |  3 |
| 18 | A2  | B4  |  5 |
| 19 | A3  | B4  |  2 |
| 20 | A4  | B4  |  5 |
+—-+——+——+——+

?
1
2
3
4
5
SET @EE=”;
SELECT @EE:=CONCAT(@EE,’SUM(IF(col2=\”,col2,’\”,’,col3,0)) AS ‘,col2,’,’) FROM (SELECT DISTINCT col2 FROM table_name) A;
SET @QQ=CONCAT(‘SELECT ifnull(col1,\’total\’) AS columnA,’,LEFT(@EE,LENGTH(@EE)-1),’ ,SUM(col3) AS TOTAL FROM table_name GROUP BY col1 WITH ROLLUP’);
PREPARE stmt2 FROM @QQ;
EXECUTE stmt2;

?
1
2
3
4
5
6
7
8
9
+———+——+——+——+——+——-+
| columnA | B1  | B2  | B3  | B4  | TOTAL |
+———+——+——+——+——+——-+
| A1   |  9 |  2 |  1 |  11 |  23 |
| A2   |  7 |  9 |  8 |  7 |  31 |
| A3   |  4 |  8 |  8 |  8 |  28 |
| A4   |  2 |  5 |  6 |  14 |  27 |
| total  |  22 |  24 |  23 |  40 |  109 |
+———+——+——+——+——+——-+

（2第二个字段确定的情况下使用
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
  IFNULL(col1,’total’) AS total,
  SUM(IF(col2=’B1’,col3,0)) AS B1,
  SUM(IF(col2=’B2’,col3,0)) AS B2,
  SUM(IF(col2=’B3’,col3,0)) AS B3,
  SUM(IF(col2=’B4’,col3,0)) AS B4,
  SUM(IF(col2=’total’,col3,0)) AS total
 FROM (
  SELECT col1,IFNULL(col2,’total’) AS col2,SUM(col3) AS col3
  FROM table_name
  GROUP BY col1,col2
  WITH ROLLUP
  HAVING col1 IS NOT NULL
 ) AS A
 GROUP BY col1
 WITH ROLLUP;

注： WITH ROLLUP 用于列上求和； SUM(IF(col2=’total’,col3,0)) AS total 用于行上求和。
（3第二个字段确定的情况下使用
?
1
2
3
4
5
6
7
select ifnull(col1,’total’) AS col1,
 sum(if(col2=’B1’,col3,0)) AS B1,
 sum(if(col2=’B2’,col3,0)) AS B2,
 sum(if(col2=’B3’,col3,0)) AS B3,
 sum(if(col2=’B4’,col3,0)) AS B4,SUM(col3) AS TOTAL
 from table_name
 group by col1 with rollup ;

以上这篇mysql 行列动态转换的实现(列联表,交叉表)就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持脚本之家。

\

通过存储过程实现：
发表于 2016-10-14 11:02:13 | 只看该作者 |只看大图 
　　行列转换的存储过程
初始图：
　　
 
　　效果图：
　　
 
 
　　实现过程：
　　1：建表、建数据
　　CREATE TABLE changeprice (    
　　   id bigint(20) NOT NULL auto_increment, 
　　   sid bigint(20) NOT NULL,
　　    datecreated timestamp NOT NULL default CURRENT_TIMESTAMP,
　　   price varchar(50) NOT NULL,
　　    PRIMARY KEY  (id)  
　　) ;  

　　插入数据：
　　(1,1,’2009-05-08’,’30’), 
(2,1,’2009-05-10’,’50’), 
(3,1,’2009-05-11’,’12’), 
(4,1,’2009-05-12’,’20’), 
(5,1,’2009-05-14’,’50’), 
(6,1,’2009-05-15’,’30’), 
(7,3,’2009-05-11’,’12’), 
(8,3,’2009-05-12’,’30’), 
(9,3,’2009-05-14’,’50’), 
(10,3,’2009-05-15’,’30’), 
(11,2,’2009-05-08’,’30’), 
(12,2,’2009-05-09’,’50’), 
(13,2,’2009-05-11’,’12’), 
(14,2,’2009-05-13’,’20’), 
(15,2,’2009-05-14’,’50’), 
(16,2,’2009-05-15’,’30’);
　　
　　存储过程实现：

CREATE DEFINER=root@localhost PROCEDURE test()
begin  
DECLARE done int default 0;  
DECLARE strDate DATE;  
DECLARE str varCHAR(10000) default ”;  
DECLARE cur1 CURSOR FOR select DISTINCT(DATE(datecreated)) from changeprice order by datecreated;  
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done=1;  
set done = 0;
open cur1;  
REPEAT  
FETCH cur1 INTO strDate;  
if  done <> 1 then  
set str=CONCAT(str, ‘,’, ‘SUM(if(datecreated=”’, strDate, ”’, price, 0))’, ””, strDate,””);  
end IF;  
UNTIL done = 1
END REPEAT;  
close cur1;
set @sqlString=CONCAT(’ select sid ‘, str, ’ from changeprice group by sid ‘);  
prepare sqlstmt from @sqlString;  
execute sqlstmt;  
deallocate prepare sqlstmt;  
set str=”;
end;

　　
　　编辑器运行 call test();即可
　　
 ======================================================================================
mysql行列互换
转载 2013年06月13日 09:27:01
2584
假设张学生成绩表(tb)如下:
Name Subject Result
张 语文　　74
张 数学　　83
张 物理　　93
李四 语文　　74
李四 数学　　84
李四 物理　　94
*/
/*
想变成 
姓名          语文         数学         物理   
李四          74           84           94
张          74           83           93
*/
create table tb
(
    Name     varchar(10) ,
    Subject varchar(10) ,
    Result  int
)
insert into tb(Name , Subject , Result) values(‘张三’ , ‘语文’ , 74)
insert into tb(Name , Subject , Result) values(‘张三’ , ‘数学’ , 83)
insert into tb(Name , Subject , Result) values(‘张三’ , ‘物理’ , 93)
insert into tb(Name , Subject , Result) values(‘李四’ , ‘语文’ , 74)
insert into tb(Name , Subject , Result) values(‘李四’ , ‘数学’ , 84)
insert into tb(Name , Subject , Result) values(‘李四’ , ‘物理’ , 94)
–静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when ‘语文’ then result else 0 end) 语文,
max(case subject when ‘数学’ then result else 0 end) 数学,
max(case subject when ‘物理’ then result else 0 end) 物理
from tb
group by name
/*
姓名          语文         数学         物理           
———- ———– ———– ———– 
李四          74           84           94
张          74           83           93
*/
–动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql varchar(8000)
set @sql = ‘select Name as ’ + ‘姓名’
select @sql = @sql + ’ , max(case Subject when ”’ + Subject + ”’ then Result else 0 end) [’ + Subject + ‘]’
from (select distinct Subject from tb) as a
set @sql = @sql + ’ from tb group by name’
exec(@sql) 
/*
姓名          数学         物理         语文           
———- ———– ———– ———– 
李四          84           94           74
张          83           93           74
*/—————————————————————–
/*加个平均分，总分
姓名          语文         数学         物理         平均分                 总分           
———- ———– ———– ———– ——————– ———– 
李四          74           84           94           84.00                 252
张          74           83           93           83.33                 250
*/
–静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when ‘语文’ then result else 0 end) 语文,
max(case subject when ‘数学’ then result else 0 end) 数学,
max(case subject when ‘物理’ then result else 0 end) 物理,
cast(avg(result*1.0) as decimal(18,2)) 平均分,
sum(result) 总分
from tb
group by name
/*
姓名          语文         数学         物理         平均分                 总分           
———- ———– ———– ———– ——————– ———– 
李四          74           84           94           84.00                 252
张          74           83           93           83.33                 250
*/
–动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql1 varchar(8000)
set @sql1 = ‘select Name as ’ + ‘姓名’
select @sql1 = @sql1 + ’ , max(case Subject when ”’ + Subject + ”’ then Result else 0 end) [’ + Subject + ‘]’ from (select distinct Subject from tb) as a
set @sql1 = @sql1 + ’ , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name’  exec(@sql1) 
/*
姓名          数学         物理         语文         平均分                 总分           
———- ———– ———– ———– ——————– ———– 
李四          84           94           74           84.00                 252
张          83           93           74           83.33                 250
*/
/*
如果上述两表互相换一下：即
姓名 语文 数学 物理
张 74　　83　　93
李四 74　　84　　94
想变成 
Name        Subject Result       
———- ——- ———– 
李四          语文       74
李四          数学       84
李四          物理       94
张          语文       74
张          数学       83
张          物理       93
*/
create table tb1
(
    姓名 varchar(10) ,
    语文 int ,
    数学 int ,
    物理 int
)
insert into tb1(姓名 , 语文 , 数学 , 物理) values(‘张三’,74,83,93)
insert into tb1(姓名 , 语文 , 数学 , 物理) values(‘李四’,74,84,94)
select * from
(
  select 姓名 as Name , Subject = ‘语文’ , Result = 语文 from tb1 
  union all
  select 姓名 as Name , Subject = ‘数学’ , Result = 数学 from tb1
  union all
  select 姓名 as Name , Subject = ‘物理’ , Result = 物理 from tb1
) t
order by name , case Subject when ‘语文’ then 1 when ‘数学’ then 2 when ‘物理’ then 3 when ‘总分’ then 4 end

[Err] 1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)

解决方案很简单：
   1.root登录MySQL，以下命令必须具有root权限。
   2.执行mysql>SET GLOBAL log_bin_trust_function_creators = 1;
      
MySQL 错误1418 的原因分析及解决方法
 (2012-06-11 15:22:18)
标签： 
杂谈
分类： sqlserver

具体错误：
 
 使用mysql创建、调用存储过程，函数以及触发器的时候会有错误符号为1418错误。

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,or READS SQL DATA in its declaration and binary logging is enabled(you might want to use the less safe log_bin_trust_function_creators variable)

 经过一番百度之后，总结如下：

因为CREATE PROCEDURE, CREATE FUNCTION, ALTER PROCEDURE,ALTER FUNCTION,CALL, DROP PROCEDURE, DROP FUNCTION等语句都会被写进二进制日志,然后在从服务器上执行。但是，一个执行更新的不确定子程序(存储过程、函数、触发器)是不可重复的，在从服务器上执行(相对与主服务器是重复执行)可能会造成恢复的数据与原始数据不同，从服务器不同于主服务器的情况。

为了解决这个问题，MySQL强制要求：
在主服务器上，除非子程序被声明为确定性的或者不更改数据，否则创建或者替换子程序将被拒绝。这意味着当创建一个子程序的时候，必须要么声明它是确定性的，要么它不改变数据。

声明方式两种：
第一种:声明是否是确定性的
DETERMINISTIC和NOT DETERMINISTIC指出一个子程序是否对给定的输入总是产生同样的结果。
如果没给定任一特征，默认是NOT DETERMINISTIC，所以必须明确指定DETERMINISTIC来声明一个子程序是确定性的。 
这里要说明的是:使用NOW() 函数（或它的同义）或者RAND() 函数不会使一个子程序变成非确定性的。对NOW()而言，二进制日志包括时间戳并会被正确的执行。RAND()只要在一个子程序内被调用一次也可以被正确的复制。所以，可以认为时间戳和随机数种子是子程序的确定性输入，它们在主服务器和从服务器上是一样的。
第二种:声明是否会改变数据  
CONTAINS SQL, NO SQL, READS SQL DATA, MODIFIES SQL用来指出子程序是读还是写数据的。
无论NO SQL还是READS SQL DATA都指出，子程序没有改变数据，但是必须明确地指定其中一个，因为如果任何指定，默认的指定是CONTAINS SQL。
默认情况下，如果允许CREATE PROCEDURE 或CREATE FUNCTION 语句被接受，就必须明确地指定DETERMINISTIC 或 NO SQL与READS SQL DATA 中的一个，否则就会产生1418错误。

解决方法：

解决办法也两种，
第一种是在创建子程序(存储过程、函数、触发器)时，声明为DETERMINISTIC或NO SQL与READS SQL DATA中的一个，
例如:
CREATE DEFINER = CURRENT_USER PROCEDURE NewProc()
    DETERMINISTIC
BEGIN
 #Routine body goes here…
END;

第二种是信任子程序的创建者，禁止创建、修改子程序时对SUPER权限的要求，设置log_bin_trust_routine_creators全局系统变量为1。设置方法有三种:

1.在客户端上执行SET GLOBAL log_bin_trust_function_creators = 1;
2.MySQL启动时，加上–log-bin-trust-function-creators选贤，参数设置为1
3.在MySQL配置文件my.ini或my.cnf中的[mysqld]段上加log-bin-trust-function-creators=1

ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘123456’;

mysql 8.0.11 用Navicat远程无法连接 解决之道
2018年05月24日 00:59:58
阅读数：227
症状：
安装了mysql 8.0.11 之后本地可以登录，但是远程第三方工具无法连接，防火墙已经放通的，
解决之道：
首先登陆到mysql命令行：
mysql -u root -p
1
进入之后择mysql库，用户信息都存在这个库的user表中
use mysql;
1
select host, user, authentication_string, plugin from user
1
可以看到，用户对应的主机是localhost，而不是%，所以不能连接。 
处理方法： 
1、授权root用户可以远程登陆
GRANT ALL ON . TO ‘root’@’%’;
1
2、刷新权限
flush privileges;
1
3、修改加密规则
ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘yourpassword’ PASSWORD EXPIRE NEVER;
1
4、更新 root 用户密码
ALTER USER ‘root’@’%’ IDENTIFIED WITH mysql_native_password BY ‘yourpassword’;
1
5、刷新权限
FLUSH PRIVILEGES;
1
测试连接。用 Navicat 连接数据库就可以正常连接了。
另，给普通用户远程连接的权限： 
1、授权 myuser 用户对指定库的所有表，所有权限并设置远程访问
GRANT ALL ON 指定库.* TO ‘myuser’@’%’;
1
2、更新 该 用户密码
ALTER USER ‘myuser’@’%’ IDENTIFIED WITH mysql_native_password BY ‘yourpassword’;
1
3.刷新权限
FLUSH PRIVILEGES;