标签归档:mysql

mysql连接字符串—远程连接用IP地址 而非只是localhost时

r on ‘192.168.1.101’ (10061)

最新的可解决方法如下:(最重要的步骤–>红色字体标识出了)

解决MYSQL数据库无法使用IP访问本地的方法
MYSQL数据库缺省安装后,其默认用户名ROOT如果只能以<localhost>或<127.0.0.1>方式访问主机,即通过外部IP地址访问返回如下内容:
ERROR 1130 (): #HY000Host ‘XXXXXX’ is not allowed to connect to this MySQL server
可以增加一个用户,给其权限,允许由局域网或互联网进行外部访问,方法如下:
1。在运行中输入CMD,确定,进入文本方式。
2。输入mysql -h localhost -u root -p 回车,使用ROOT用户登录。
3。输入use mysql; 显示Database changed,选择MYSQL系统库。
4。假定我们现在增加一个’goldeye2000’用户,密码为’1234567’,让其能够从外部访问MYSQL。输入
grant all on * to ‘goldeye2000’ identified by ‘1234567’;
ALL代表所有权限。
5。现在看看用户表内容。输入select user,host from user ; 可以看到”goldeye2000″用户已经加进去了,并且其权限为’% ‘,’grande’,’localhost ‘。
6。退出MYSQL,输入QUIT;回车
7。我们现在可以用goldeye2000用户在局域网或互联网中以IP方式访问了。
mysql -h 192.168.0.115 -u goldeye2000 -p

二、bind-address = 127.0.0.1 注释掉即可

C# 执行Mysql数据库脚本 创建数据库和表

开发过程中可能需要将数据库导入到生产库中,我们可以通过MySQL workbench将数据库导出成sql文件,然后在C#代码中直接执行sql脚本,创建相应的数据库和表。

项目中需要添加引用mysql.Data.dll

CS文件中需要应用using MySql.Data.MySqlClient;

[csharp] view plain copy

  1. private void button_connTest_Click(object sender, RoutedEventArgs e)
  2. {
  3.     string connStr = “server=192.168.3.21;user=root;database=;port=3306;password=root;”;
  4.     MySqlConnection conn = new MySqlConnection(connStr);
  5.     try
  6.     {
  7.         textbox_log.Text += “Connecting to MySQL…\r\n”;
  8.         conn.Open();
  9.         FileInfo file = new FileInfo(“D:/endb.sql”);  //filename是sql脚本文件路径。
  10.         string sql = file.OpenText().ReadToEnd();
  11.         MySqlScript script = new MySqlScript(conn);
  12.         script.Query = sql;
  13.         int count = script.Execute();
  14.         textbox_log.Text += “Executed “ + count + ” statement(s)\r\n”;
  15.         textbox_log.Text += “Delimiter: “ + script.Delimiter+“\r\n”;
  16.         //textbox_log.Text += “Query: ” + script.Query + “\r\n”;
  17.     }
  18.     catch (Exception ex)
  19.     {
  20.         textbox_log.Text += ex.ToString();
  21.     }
  22.     conn.Close();
  23.     textbox_log.Text += “Execute Successfully.”;
  24. }


执行完成后,就可以看到新的数据库自动创建成功了。

如果只是简单的执行建表或者其他语句,那么数据库连接字符串将要写对应的数据库名,如:

[csharp] view plain copy

  1. string connStr = “server=192.168.3.21;user=root;database=MyDB;port=3306;password=root;”;
  2. MySqlConnection conn = new MySqlConnection(connStr);

相关知识:通过Mysql WorkBench将数据库导出成sql脚本,选择菜单–>Server–>Data Export

2

MySQL绿色版的安装

由于工作需要最近要开始研究MySQL了(看来学习都是逼出来的),本人对mysql没有研究,可以说一个小白。 下面就从安装开始吧,虽然网上关于这方面的东西很多,还是需要自己把操作过程写下来。

1、数据库下载

  Mysql官方网站:http://www.mysql.com/,数据库下载地址:http://www.mysql.com/downloads/。从官方网站可以找到两种文件包,一种是exe安装程序,另一种是zip压缩包。本人喜欢清爽的方式,所以下载的是ZIP压缩包。最新的5.6.22大概350M,下载还需要oracle帐号,自己注册一个好了。

2、数据库安装

解压出下载的文件mysql-5.6.22-win32.zip(有x86和x64两个版本)到任一目录,防止出现未知问题,最好放在非系统盘的非中文目录下,我的位置C:\Program Files\mysql-5.6.22-win32。打开文件夹复制一份my-default.ini为my.ini的配置文件。

打开my.ini文件,相关配置修改如下

复制代码

1 # For advice on how to change settings please see
 2 # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
 3 # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
 4 # *** default location during install, and will be replaced if you
 5 # *** upgrade to a newer version of MySQL.
 6 
 7 [mysqld]
 8 
 9 # Remove leading # and set to the amount of RAM for the most important data
10 # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
11  innodb_buffer_pool_size = 128M
12 
13 # Remove leading # to turn on a very important data integrity option: logging
14 # changes to the binary log between backups.
15 # log_bin
16 
17 # These are commonly set, remove the # and set as required.
18 #mysql根目录
19  basedir ="C:\Program Files\mysql-5.6.22-win32"
20 #数据文件存放目录
21  datadir ="C:\Program Files\mysql-5.6.22-win32\data"
22 # port = .....   端口,默认3306
23 # server_id = .....  服务实例的唯一标识
24 
25 
26 # Remove leading # to set options mainly useful for reporting servers.
27 # The server defaults are faster for transactions and fast SELECTs.
28 # Adjust sizes as needed, experiment to find the optimal values.
29 # join_buffer_size = 128M
30 # sort_buffer_size = 2M
31 # read_rnd_buffer_size = 2M 
32 
33 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
34 #服务端的编码方式
35 character-set-server=utf8
36 [client]
37 #客户端编码方式,最好和服务端保存一致
38 loose-default-character-set = utf8
39 
40 [WinMySQLadmin]  
41 Server = "C:\Program Files\mysql-5.6.22-win32\bin\mysqld.exe"

复制代码

上面设置为了保证服务正常运行,根据自己需要调整参数,修改后要重启服务。

3、注册Mysql服务

开始——运行——cmd,然后cd到mysql安装目录的bin下面,我的目录就是“C:\Program Files\mysql-5.6.22-win32\bin”,然后执行mysqld -install,提示服务安装成功!运行services.msc一看,确实有一个名为MySQL的服务了,启动它。有时启动失败,根据情况调整参数即可。

到这儿mysql安装就算完成了,其实还挺简单的,但如果没有操作过,整个过程估计要花不少时间,也是成长必须的过程。

4、登录维护Mysql

安装成功了,也启动了,怎么登录啊?用户名和密码是什么 ?

MySQL的默认用户名是root,密码为空。

怎么登录?还是开始——运行——cmd,cd到bin目录下,然后执行“mysql -u root -p”,提示输入密码,由于第一次运行,密码为空可以直接回车。

还是给数据库设个密码吧。输入exit退出登录。然后运行运行C:\Program Files\mysql-5.6.22-win32\bin\mysqladmin -uroot -p password <新密码> ,将<新密码>替换为你的自定义密码,然后按回车。这时会提示输入密码,其实是指的原密码,原密码因为是空,所以这里再回车即可完成设置。然后用上面的方法和新密码登入mysql,即可正常工作。

运行”show variables like ‘%version%'” 查看数据库相关信息

至此,整个数据库的安装完成。

虽然mysql客户端能完成所有关于数据库操作,但是黑黑的命令行界面还是让很多人望而却步,学习曲线直线上升,在此给大家推荐一个工具Navicate for mysql,个人感觉非常好用,各种操作一目了然,还有个SQLyog也不错。

 

6、开启远程登录

mysql安装完成后默认只能本机(就是localhost)登录, 我们需要开启远程登录才方便使用. 开启方法有很多,百度一大把, 我说一下测试通过的方法

 

复制代码

x:\>mysql -u root -p 密码   //登录系统
mysql> use mysql;       //切换数据库
mysql> update user set host = '%' where user = 'root';  
//上面这句话有时出现一个错误  ERROR 1062 (23000): Duplicate entry '%-root' for key 'PRIMARY'

//不过这时你查询一下user表发现已经更新一条记录了,下面这句话
mysql> select host, user from user;

//直接执行这句,其实就是刷新权限
mysql> flush privileges;

复制代码

 

再测试远程连接试试, 已经可以了

SQL Server 迁移数据到MySQL

一、背景

由于项目开始时候使用的数据库是SQL Server,后来把存储的数据库调整为MySQL,所以需要把SQL Server的数据转移到MySQL;由于涉及的表比较多,所以想在MySQL中生成对应表并导入数据;

上网找了些资料,如:将ACCESS和MSSQL导入MYSQL中MySQL Migration 实现 MSSQL 到 MySQL数据迁移,虽然不知道里面的做法是否可以成功转移,但是里面的过程比较复杂,没有去尝试,后来自己找到了方法,最重要就是简单和准确(暂时没发现明显的BUG),这里分享给大家。

 

二、转移数据

我使用了MySQL的Client的工具SQLyog,这个工具的安装很简单。安装完成之后点击需要导入的目标数据库,点击右键【Import】->【Import Extenal Data】;

clip_image002

(Figure1:Import)

选择【Start a new job】,点击【下一步】;

clip_image003

(Figure2:Start a new job)

下面就是DSN的设置界面,如果你的下来列表中没有需要导出SQL Server数据库的DSN,那么需要通过【Create a New DSN】来创建一个新的DSN连接到SQL Sever;

clip_image004

(Figure3:Create a New DSN)

clip_image005

(Figure4:创建新数据源)

clip_image006

(Figure5:选择数据源类型)

把上面的设置保存为一个local.dsn文件;

clip_image007

(Figure6:选择数据源保存路径)

clip_image008

(Figure7:选择SQL Server服务器)

clip_image009

(Figure8:设置SQL Server帐号和密码)

clip_image010

(Figure9:选择SQL Server数据库)

clip_image011

(Figure10:测试数据源)

clip_image012

(Figure11:测试成功)

选中【File DSN】,在浏览中选择我们刚刚创建好的DSN,接着填写登录到SQL Server的帐号和密码;

clip_image013

(Figure12:选择DSN)

下一步,选择目标MySQL服务器,填写IP地址和帐号密码,并且需要选择目标数据库;

clip_image014

(Figure13:设置MySQL帐号和密码目标数据库)

这一步类似SQL Server的导入导出功能,这里可以拷贝一个表或者使用SQL脚本过滤数据;

clip_image015

(Figure14:表拷贝)

上面的全部过程就是为创建SQL Server与MySQL的一个管道,接下来就是最为重要的设置SQL Server表与MySQL表之间的对应关系了;里面包括了SQL Server表字段与MySQL表字段之间的对应关系【Map】,高级选项【Advanced】,过滤【WHERE】。

clip_image016

(Figure15:选择表对应关系)

下图Figure16,数据源【Source】,描述【Destination】,特别注意这里数据类型【Type】,这里的意思是转换目标的数据类型,但是不需要跟目标表的一样,因为这里是做为一个临时存储的数据类型,类似FindOn在SQL Server中是datetime,这里转换为MySQL的timestamp,其实MySQL目标表的数据类型是datetime,这样的设置也是可以转换成功的。

clip_image018

(Figure16:表字段转换)

clip_image019

(Figure17:高级选项)

clip_image020

(Figure18:Error)

clip_image021

(Figure19:Log)

clip_image022

(Figure20:执行信息)

clip_image023

(Figure21:执行结果)

clip_image024

(Figure22:原始SQL Server的数据列表)

clip_image025

(Figure23:转移到MySQL的数据列表)

对比下Figure20与Figure21,发现我们数据已经全部转移成功了;

mysql查询当天所有数据sql语句

在mysql中查询当天数据我们使用到如year,month,day函数是一种做法,还有一种利用date(regdate) = curdate()函数,当然我们也可以用其它方法,下面我来总结一下。

mysql查询当天的所有信息:

 代码如下 复制代码
select * from test where year(regdate)=year(now()) and month(regdate)=month(now()) and day(regdate)=day(now())

这个有一些繁琐,还有简单的写法:

 代码如下 复制代码
select * from table where date(regdate) = curdate();

另一种写法没测试过

查询当天的记录

 代码如下 复制代码
select * from hb_article_view where TO_DAYS(hb_AddTime) = TO_DAYS(NOW())

date()函数获取日期部分, 扔掉时间部分,然后与当前日期比较即可
补充:本周、上周、本月、上个月份的数据
查询当前这周的数据

 代码如下 复制代码
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,’%Y-%m-%d’)) = YEARWEEK(now());

查询上周的数据

 代码如下 复制代码
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,’%Y-%m-%d’)) = YEARWEEK(now())-1;

查询当前月份的数据
select name,submittime from enterprise where date_format(submittime,’%Y-%m’)=date_format(now(),’%Y-%m’)

查询距离当前现在6个月的数据

 代码如下 复制代码
select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();

查询上个月的数据

 代码如下 复制代码
select name,submittime from enterprise where date_format(submittime,’%Y-%m’)=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),’%Y-%m’)

select * from `user` where DATE_FORMAT(pudate,’%Y%m’) = DATE_FORMAT(CURDATE(),’%Y%m’) ;

select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,’%y-%m-%d’)) = WEEKOFYEAR(now())

select *
from user
where MONTH(FROM_UNIXTIME(pudate,’%y-%m-%d’)) = MONTH(now())

select *
from [user]
where YEAR(FROM_UNIXTIME(pudate,’%y-%m-%d’)) = YEAR(now())
and MONTH(FROM_UNIXTIME(pudate,’%y-%m-%d’)) = MONTH(now())

select *
from [user]
where pudate between 上月最后一天
and 下月第一天
mysql查询多少秒内的数据

 

 代码如下 复制代码
SELECT count( * ) AS c, sum( if( logusertype =2, logusertype, 0 ) ) /2 AS a, sum( if( logusertype =3, logusertype, 0 ) ) /3 AS b
FROM testlog WHERE UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP( logendtime )<=30

查询30秒内记录的总数,loguser等于2的记录的总数和,和 loguser等于3的记录的总数.

if( logusertype =2, logusertype, 0 ) 如果logusetype等于2 就在logusertype上累加,否则加0。

sum( if( logusertype =2, logusertype, 0 ) ) 把logusertype都累加起来。

sum( if( logusertype =2, logusertype, 0 ) ) /2 AS a, 除以2是统计个数。

UNIX_TIMESTAMP(NOW())计算当前时间的秒数,

UNIX_TIMESTAMP( logendtime )计算logendtime的秒数

Oracle如何实现与mysql中find_in_set的函数

find_in_set(str,strList),寻找str在strList中的位置。
Sql代码 收藏代码
— 返回结果为1,
SELECT FIND_IN_SET(‘1′,’1,2,3’) FROM DUAL;

Sql代码 收藏代码
— 返回结果为2
SELECT FIND_IN_SET(‘1′,’2,1,3’) FROM DUAL;
具体创建函数如下:
Sql代码 收藏代码
create or replace FUNCTION FIND_IN_SET(piv_str1 varchar2, piv_str2 varchar2, p_sep varchar2 := ‘,’)
RETURN NUMBER IS
l_idx number:=0; — 用于计算piv_str2中分隔符的位置
str varchar2(500); — 根据分隔符截取的子字符串
piv_str varchar2(500) := piv_str2; — 将piv_str2赋值给piv_str
res number:=0; — 返回结果
res_place number:=0;– 原字符串在目标字符串中的位置
BEGIN
— 如果字段是null 则返回0
IF piv_str2 IS NULL THEN
RETURN res;
END IF;
— 如果piv_str中没有分割符,直接判断piv_str1和piv_str是否相等,相等 res_place=1
IF instr(piv_str, p_sep, 1) = 0 THEN
IF piv_str = piv_str1 THEN
res_place:=1;
res:= res_place;
END IF;
ELSE
— 循环按分隔符截取piv_str
LOOP
l_idx := instr(piv_str,p_sep);

res_place := res_place + 1;
— 当piv_str中还有分隔符时
IF l_idx > 0 THEN
— 截取第一个分隔符前的字段str
str:= substr(piv_str,1,l_idx-1);
— 判断 str 和piv_str1 是否相等,相等则结束循环判断
IF str = piv_str1 THEN
res:= res_place;
EXIT;
END IF;
piv_str := substr(piv_str,l_idx+length(p_sep));
ELSE
— 当截取后的piv_str 中不存在分割符时,判断piv_str和piv_str1是否相等,相等 res=res_path
IF piv_str = piv_str1 THEN
res:= res_place;
END IF;
— 无论最后是否相等,都跳出循环
EXIT;
END IF;
END LOOP;
— 结束循环
END IF;
— 返回res
RETURN res;
END FIND_IN_SET;
对于某些查询mysql只需写出表达式就可以了,但是我写的oracle的需要写出表达式出来
例如:
Sql代码 收藏代码
select * from tableA a left join tableB b on FIND_IN_SET(a.id ,b.aid) ; — mysql这样写就可以了

Sql代码 收藏代码
select * from tableA a left join tableB b on FIND_IN_SET(a.id ,b.aid) >0; — oracle就要写出具体的表达式

mysql与oracle 表字段定义比较

MySQLOracle 表字段定义比较

 

  说明 mysql oracle
VARCHAR 变长字符串 VARCHAR[0-65535]

定义长度默认按字符长度计算,如果是GBK编码的汉字将占用2个字节

VARCHAR2[1-4000]
VARCHAR是VARCHAR2的同义词

定义默认按字节长度计算

TINYINT

SMALLINT

MEDIUMINT

INT
BIGINT

整数 TINYINT(-128-127)

SMALLINT(-32768-32767)

MEDIUMINT(-8388608-8388607)

INT(-2147483648-2147483647)
BIGINT(-9223372036854775808-9223372036854775807)

无专用类型,

TINYINT可以用NUMBER(3,0)代替

SMALLINT可以用NUMBER(5,0)代替

MEDUIMINT可以用NUMBER(7,0)代替

INT可以用NUMBER(10,0)代替

BIGINT可以用NUMBER(20,0)代替

 

ORACLE中有SMALLINT,INT,INTEGER类型,不过这是NUMBER(38,0)的同义词

DECIMAL
NUMERIC
数值类型 DECIMAL[1-65[,0-30]]
NUMERIC是DECIMAL的同义词
NUMBER 可表示数范围: 1*10^-130至1*10^126

NUMBER([1-38][,-84-127])

 

DECIMAL、NUMERIC、DEC是NUMBER的同义词

FLOAT 浮点型 FLOAT(D,M) oracle10g开始增加BINARY_FLOAT类型

10g以前无专用类型,可以用NUMBER代替

ORACLE中有FLOAT和REAL类型,不过这是NUMBER的同义词

DOUBLE 双精度浮点型 DOUBLE(D,M) oracle10g开始增加BINARY_DOUBLE类型

10g以前无专用类型,可以用NUMBER代替

ORACLE中有DOUBLE PRECISION类型,不过这是NUMBER的同义词

BIT 位类型 BIT(1-64)
DATETIME 日期类型 DATE,3字节存储,只存储日期,没有时间,支持范围是[1000-01-01]至[9999-12-31]
TIME,3字节存储,只存储时间,没有日期,支持范围是[-838:59:59]至[838:59:59]
DATETIME,占8字节存储,可表示日期和时间,支持范围是[1000-01-01 00:00:00]至[9999-12-31 23:59:59]
TIMESTAMP,占4字节存储,可表示日期和时间,范围是[1970-01-01 00:00:00]至[2038-01-19 03:14:07]
DATE类型
7字节存储,可表示日期和时间,支持范围是[-4712-01-01 00:00:00]至[9999-12-31 23:59:59]
TIMESTAMP 高精度日期 5.6.4以前不支持小数秒精度
5.6.4开始TIME,DATETIME,TIMESTAMP支持,最多可以6位小数秒,也就是微秒级别
TIMESTAMP[0-9]
占用空间7-11个字节,当小数秒精度为0时与DATE类型相同,小数秒最高精度可达9位,也就是纳精度
YEAR 年份 YEAR,1字节存储,只存储年份,支持范围是[1901]至[2155] 无对应类型,可以用NUMBER(3,0)代替
CHAR 定长字符串 CHAR[0-255],定义长度默认按字符长度计算,最大保存255字符 CHAR[1-2000]

定义默认按字节长度计算

UNSIGNED 无符号说明 支持,用于数值类型 不支持
CLOB 大字符串,一般用于存储文本文件或超大描述及备注类信息 TINYTEXT 最大支持255个字节
TEXT最大支持65535个字节
MEDIUMTEXT最大支持16MB个字节
LONGTEXT最大支持4GB字节

字段不支持默认值

支持(CLOB)
oracle10g以前最大支持4GB个字节

oracle10g开始最大支持4GB个数据块,数据块大小为2KB-32KB

oracle还有一个LONG类型,是早期的存储大字符串类型,最大支持2GB字节,现已不推荐使用

BLOB 大二进制对象,一般用于存储文件或图片数据 TINYBLOB 最大支持255个字节
BLOB最大支持65535个字节
MEDIUMBLOB最大支持16MB个字节

LONGBLOB最大支持4GB字节

 

字段不支持默认值

支持(BLOB)

oracle10g以前最大支持4GB个字节

oracle10g开始最大支持4G个数据块,数据块大小为2KB-32KB

oracle还有一个LONG RAW类型,是早期的存储二进制类型,最大支持2GB字节,现已不推荐使用

BINARY 二进制信息 BINARY(0-255),定长
VARBINARY(0-65535),变长
RAW(1-2000)
ENUM 枚举类型 ENUM(v1,v2,v3,…),最多65535个元素 不支持
SET 集合类型 SET(v1,v2,v3,…),最多64个元素 不支持
NATIONAL CHAR 国际化字符集类型,较少使用 无,MYSQL可以对每个字段指定字符编码 支持
NCHAR(1-2000)
NVARCHAR(1-4000)
NCLOB
BFILE 外部文件指针类型 不支持 支持
文件大小最大4GB
文件名称最长255字符
自定义数据类型   不支持 支持
XML类型   不支持 支持
自增类型 自动增长类型 支持
使用简单
不支持
一般使用SEQUENCE解决,用法与自增类型差别较大,使用较复杂,但能实现非常灵活的应用,包括字符自增主键、全局主键等等
字段默认值表达式   不支持函数和表达式
TEXT和BLOB字段类型不支持默认值
支持函数和表达式
字段顺序修改   支持,例如,把emp表的id字段顺序放在name字段后面:
alter table emp modify column id varchar(20) after name;
不支持,只能重建表或字段
虚拟字段 虚拟字段是一个逻辑字段定义,其结果值通常是一个表达式,并在表中存储物理值,不占用空间,主要用于简化查询逻辑。比如有一个商品销售表有单价和数量两个字段,那可以建一个虚拟字段金额,其表达式=单价*数量 不支持 11g支持,例:
create table sales
(
id       number,
quantity number,
price    number,
amount   GENERATED always as (quantity*price) virtual
);
表字段数限制   INNODB 最大1000个字段
所有字段总定义长度不能超过65535字节
所有固定长度字段的总长度不超过半个数据块大小(数据块大小一般为16K)
最大1000个字段

 

Oracle和MySQL在SQL语句方面的区别

因为公司的系统同时使用到了Oracle和MySQL数据库,经常在编写SQL时会遭遇不兼容问题,在此进行整理和总结。

1 数据类型

MySQL Oracle Note
int/double number 数值型
varchar varchar2 小文本型
text varchar2 对于普通文本大于255,且小于4000的列
text blob 对于大于4000的comment
longblob blob

 

2 表

2.1 创建表(同)

create table tableName(

columnName1 int,

columnName2 int

)

2.2 删除表(异)

MySQL:

drop table if exists tableName

Oracle:

drop table tableName

注:Oracle没有if exists关键字,也没用类似if exists的SQL语法。

 

3 列

3.1 添加列(异)

MySQL:

A. alter table tableName add column columnName1 int;

B. alter table tableName add column columnName1 int, add column columnName2 int;

注:其中关键字column可有可无。

Oracle:

A. alter table tableName add columnName1 int;

B. alter table tableName add (columnName1 int);

C. alter table tableName add (columnName1 int, columnName2 int);

注:对于A,只有添加单列的时候才可使用,对于添加多列时需要使用C,不能像MySQL那样重复使用add column关键字。

3.2 删除列(异)

MySQL:

A. alter table tableName drop column columnName1

B. alter table tableName drop column columnName1, drop column columnName2

注:其中关键字column可有可无。

Oracle:

A. alter table tableName drop column columnName2

B. alter table tableName drop (columnName1)

C. alter table tableName drop (columnName1,columnName2)

注:对于A,只有删除单列的时候才可使用,对于删除多列时需要使用C,不能像MySQL那样重复使用drop column关键字。

3.3 修改列名(异)

MySQL:

alter table tableName change column columnNameOld columnNameNew columnType;

Oracle:

alter table tableName rename column columnNameOld to columnNameNew;

3.4 修改列类型(说明)

Oracle中,在列有数据的时候,无法修改列类型;没有数据时可以。

MySQL中,无论列是否有数据都可以修改列类型。

但是当有数据是,直接修改列类型都可能对数据造成丢失等,所以一般需要结合具体的业务来对列数据做处理后,再修改列类型类型。所以修改列的类型并非使用SQL语句进行一步到位的修改,而是通过以下流程:

A. 添加临时列

B. 将需要更改的列的值经过类型转换的验证后,赋值给临时列

C. 删除原有列

D. 将临时列的列名修改为原有列列名

 

4 索引

在整个数据库内,MySQL的索引可以同名,也就是说MySQL的索引是表级别的;但是Oracle索引不可以同名,也就是说Oracle的索引是数据库级别的。

4.1 创建索引(同)

create index indexName on tableName (columnName);

4.2 删除索引(异)

MySQL:

alter table tableName drop index indexName

Oracle:

drop index indexName

4.3 查询表的索引(异)

MySQL:

show index from tableName

Oracle:

select index_name, table_name, column_name from user_ind_columns where table_name=’ tableName ‘

 

5 空字符串问题

Oracle中空字符串”就是null(也就是说,只有null,没有空字符),而MySQL是区分null和”的。

对于使用语句:select * from table1 where user_name <> ”来查询列user_name不为空(不为null且不为空字符)时,Oracle会查不出任何结果,而MySQL可以正常运行。这里MySQL之所以可以得到正确结果,还因为比较符号<>会先将列为null的内容进行过滤,然后再比较内容是否为空字符串。

这就要求一方面,以后在编写代码的时候,尽量保证不会往数据库插入空字符串”这样的值,要么保持有数据,要么保持为null。另外,对于MySQL中已经同时存在Null和”时,所有判断是否为null或者”的地方改为判断列的长度是否为0。

 

6 Left Join + Order By问题

对于以下SQL:

[sql] view plain copy

  1. SELECT * FROM (
  2. SELECT DISTINCT v.fld_name,v.BASIN_NAME,v.COUNTRY,v.REGION,v.OPR_CMPNY,v.main_hc_type,v.fld_id,uf.username,v.fld_id as resource_id
  3. FROM integrated_fld_view v, user_fldid uf, repax_udm_user_info ruui
  4. WHERE …(省略)
  5. ORDER BY v.MAIN_HC_TYPE ASC ,v.FLD_NAME
  6. ) a
  7. LEFT JOIN (SELECT in_faks, resource_id FROM user_filter_status WHERE username = ‘XXX:200202030002’) ufs ON ufs.resource_id = a.resource_id

 

MySQL能够正确的被Order By进行排序,而Oracle则不行,必须要把放在第一个子查询中的Order By语句提取出来放到整个SQL的最后才可以,原因终于查清楚了:是因为使用了DISTINCT关键字的缘故,有这个关键字Order By里面的列必须出现在Select里面。

 

7 AS关键字

在为表名或者列名定义别名时,我们会使用到AS关键字:

  1. SELECT DISTINCT v.fld_name,v.main_hc_type,v.fld_id,uf.username,v.fld_id as resource_id,ruui.company
  2. FROM integrated_fld_view as v, user_fldid as uf, repax_udm_user_info as ruui

 

上面的SQL在MySQL中能够正确运行,但是在Oracle中不能:因为Oracle中为表取别名时不需要也不能加AS关键字。

实际上在MySQL和Oracle中定义表或者列别名时,都可以不使用AS关键字,只要有一个空格即可:

  1. SELECT DISTINCT v.fld_name,v.main_hc_type,v.fld_id,uf.username,v.fld_id resource_id,ruui.company
  2. FROM integrated_fld_view v, user_fldid uf, repax_udm_user_info ruui

 

mysql 数据导入 oracle数据库的尝试

我有一个十分久远的mysql数据库脚本文件(.sql格式),现在我想将此数据库导入oracle数据库中,在网上搜集了一些解决办法,其中利用Dbmover for mysql to oracle这个工具比较简单,所以,就用了这个工具。

首先,将sql脚本中的数据库结构与数据导入mysql数据库中,利用source命令。可是,在导入的过程中,提示ERROR1366 错误。

在网上搜了一些内容,得到的结论是sql文件的编码与数据库的字符集编码不一致导致的。下面就开始了修改编码,使它们能够统一。

先将sql脚本利用记事本工具另存为UTF-8编码格式

然后,将sql数据库中的字符集编码统一为utf8

最后利用source导入,成功。

可是,在cmd中查看表中的一些汉字信息时,还是出现来了乱码,但是在navicat中,却可以正确显示。

我思考我的数据本身是没有问题的,是不是mysql某个部分的编码需要修改,所以对character_set_client、character_set_server、character_set_connection、character_set_results通通都试了一遍,将其值改为gbk,然后在cmd中用select查找显示。最终,在测试character_set_results时,在cmd中查看汉字可正常显示,在navicat中亦可正常显示,问题解决。

下面,运行Dbmover for mysql to oracle工具:

 

首先,输入mysql服务器的用户名和密码:

 

接着进行mysql的连接设置:

然后,选择oracle服务器的连接方式,选择客户端模式即可,因为服务器就安装在本机,客户端oraclelistener默认是运行的。

 

 

然后,配置oracle的登录名、密码、服务名(数据库名)。

最后,选择要转换的表,进行转换即可。

但是,在第一次尝试时,出现了8个错误。

经过检查,发现主要是ORA-00910错误,到网上搜了下,解释如下:

复制代码
Oracle Database Error ORA-00910
ORA-00910 specified length too long for its datatype

问题
for datatypes CHAR and RAW, the length specified was > 2000; otherwise, the length specified was > 4000.

解决
use a shorter length or switch to a datatype permitting a longer length such as a VARCHAR2, LONG CHAR, or LONG RAW
复制代码

我就开始检查,转换后的类型,有没有超出其在oracle下预定的范围,发现了varchar(10000)类型。而,varchar(10000)类型的,范围是:

复制代码
Variable-length character string having maximum length size bytes or characters. Maximum size is 4000 bytes or characters, and minimum is 1 byte or 1 character. You must specify size for VARCHAR2.
来自:http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm#i45694
复制代码

 

所以,在先将mysql中哪些越界的字段修改 alter table操作,

然后重新转换,这一次转换是成功的。

 

Dbmover for Mysql to Oracle 是高效的将Mysql导入到Oracle数据库的工具。
使用DBMover可以灵活定义Mysql和Oracle之间表和字段的对照关系,也可以在DBMover创建一个查询,把查询结果当作源表转入到Oracle中。
Dbmover for Mysql to Oracle 可以定时,定周期自动运行。
支持 Oracle 8i 以后的所有版本。
支持 Mysql 3.23 以后的所有版本。
高效直接连接Oracle和Mysql数据库。甚至不需要安装客户端。
向导式操作。
可以保存配置文件。
可以同时连接Mysql和Oracle,直接将数据导入Oracle。也可以保存数据到Dump文件。
功能完善的错误处理机制。记录每一条有异常的数据。并提供工具重复执行。
全面优化数据库操作,让大数据量的处理更少等待。

 

下载地址:

 

一、环境和需求
1、环境

Mysql数据库服务器:

OS version:linux 5.3 for 64 bit

Mysql Server version: 5.0.45

Oracle数据库服务器:

OS version:linux 5.3 for 64 bit

Oracle version:oracle 11g r2

2、需求

把mysql数据库的数据转移到oracle数据库。目前mysql数据库的备份文件为.sql文件,每个表一个.sql文件,把这些文件的数据导入到oracle数据库。

二、mysql数据恢复

采用先把mysql数据库备份文件恢复到一个mysql测试库中,然后使用oracle sql developer把mysql测试库中的数据转移到oracle数据库。

mysql备份恢复到myql测试库:

因为本次试验采用的mysql备份为.sql文件,所以采用批量source处理。批量执行.sql文件,实现在mysql测试库重新建立表并恢复数据。

如果备份文件采用的是其他方式,则需要用对应的恢复办法进行恢复。

恢复操作:

[root@localhost ~]# mysql -u root -p

Enter password: —输入root用户的密码。

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 90

Server version: 5.0.45 Source distribution

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> use test;

Database changed

mysql> source actors.sql;

mysql> source act_tactics.sql;

mysql> ………………

一次把所有的.sql文件贴在mysql的命令窗口,批量执行即可。

注:因为mysql库是生产库,不能直接做实验,所以才把备份恢复到一个测试库中,进行测试,实际中如果可以直接连mysql数据库,则这一步可以省略。

三、通过oracel sql developer转换数据库

Mysql转到oracle数据库,要根据不同情况选择不同的方法:

1、  从现有的mysql库直接转到oracle数据库

这是不用恢复mysql数据库,直接可以用sql developer转到oracle里,这时要现在oracle数据库中建好用户(用户名为mysql数据库名),选好默认表空间,mysql数据就会导入到该用户下。

2、  从mysql数据库导出的sql文件导入到oracle数据库

如果是只有mysql的导出文件,则需要先把该文件恢复到一个mysql数据库中,然后再把恢复后的mysql数据库转换到oracle数据库中。

这种情况要注意oracle数据库的用户名。因为sql developer把mysql转换到oracle数据库中时,会把mysql的数据存放到一个用户下,这个用户名一定会和mysql的数据库名相同。如果oracle中已有这个用户,则数据直接导入到该用户下;如果没有这个用户,则sql developer会直接创建这样的用户,并把数据导入到该用户下。需要注意的是,sql developer默认创建的oracle数据库用户的默认表空间是user,如果不注意,很有可能会导致user表空间爆满!!!所以这种情况最好先建一个和mysql数据库名一样的oracle用户,以防止user表空间爆满影响数据导入。

1、安装oracel sql developer

首先从oracle官方网站下载oracle sql developer,下载的sql developer是没有集成jdk的,如果本机也没有安装过jdk,则需要先安装对应版本的jdk,这个可以查找sql developer的readme.html文件,里面会说明需要的jdk。

 

安装的第一步会让你选择JDK,否则无法安装。安装非常简单,这里就不做说明。

Windowd 64位sqldeveloper下载:

http://download.oracle.com/otn/java/sqldeveloper/sqldeveloper64-3.0.04.34-no-jre.zip

Windowd 32位sqldeveloper下载:

http://download.oracle.com/auth/otn/java/sqldeveloper/sqldeveloper-3.0.04.34.zip?e=1313718071&h=0b242a06885410fbb4df8b5628a804e8

安装JDK和mysql-connector-java:

jdk-6u27-windows-x64.exe下载地址(最第要用jdk-6u11以后的版本):

http://download.oracle.com/auth/otn-pub/java/jdk/6u27-b07/jdk-6u27-windows-x64.exe?e=1313726411&h=ff3cc2d66e07d7d63d6f8e9fbabc3743

mysql-connector-java各版本下载:

http://download.softagency.net/mysql/Downloads/Connector-J/

mysql-connector-java配置:

以上的mysql-connector-java-5.0.8-bin.jar就是mysql-connector-java-5.0.8.zip解压出来的文件。配置好jdbc后,即可开始数据库连接。

如果要连接sql server,则下载jtds-1.2.5-dist.zip配置即可。

2、连接数据库

连接oracle的用户要有create table权限,一般用system用户就可以。连mysql数据库因为是读取数据,用什么用户都可以(一般默认是root用户)。

转换后oracle数据库会多一个新的用户名,就是mysql的数据库名。除了这个用户,系统还会自动建一个名为EMULATION的用户,该用户可以锁定或删除都可以。

打开sqldeveloper.exe:

 

新建oracle数据库连接:

Sql developer转换数据时会产生一些字典表,这些字典表会保存到sql developer链接oracle数据库的用户中,如果这个用户的名字和Mysql数据库名字不同,则mysql数据不会保存在该用户下。

点击测试,测试连接:

点击保存:

点击连接,即可连接到oracle数据库:

 

新建mysql数据库连接:

1.)选择mysql选项卡

2.)填写mysql数据库信息

填写完进行测试,成功后点击保存,并连接到mysql数据库。

3、复制表

如果不用迁移整个数据,只是迁移表的数据,则可以直接在mysql数据库库中选中要转移的表,点“右键”选“复制到oracle”即可。此时会把表转移到sql developer链接oracle数据库的用户下,并且该用户下不能有同名的表。

不过从以往的经验看,复制表要比迁移数据库效率低,所以如果是复制所有的表,最好用移植数据库功能。

4、移植数据库

点击“工具”,选择“移植”

移植简介

选择要转换的mysql数据库,添加到列表中:

指定转换规则,可以根据自己的情况设定字段属性的转换,也可以新添加规则。不过一般选择默认的就能满足需求。

选择目标数据库

查看转换概要,点击“完成”开始转换

转换完成后需要检查数据库的各种对象是否完成,状态是否正确,尤其是表的数量一定要核对,因为有时候有些表会不能成功转换,需要手工操作。

四、修改oracle用户名

因为转换过来的数据默认存放在USERS表空间里,而且会创建一个和mysql数据库名一模一样的oracle用户,并把mysql数据库导入到该用户下。可以exp出来新用户的数据后,然后再导入到正确的用户下,这样数据也会存在正确的表空间下面。但是如果数据量很大的时候,exp/imp会很浪费时间,建议数据量大的时候不要采用这种方式。

如果要是先建好用户(用户名用mysql数据库的数据库名),定义好用户的默认表空间,然后再做mysql到oracle转换,这样就可以即把表存放到正确的位置,又可以用正确的用户名。也可以改变数据库的默认表空间防止自动创建用户的默认表空间使用user表空间:

SQL> ALTER DATABASE DEFAULT TABLESPACE mis_data;

注意:改过名字的用户,权限会继承,但是默认表空间不会继承,需要手工再设定默认表空间:

SQL> ALTER USER OA identified by oa default tablespace MIS_DATA temporary tablespace TEMP;

修改底层表 USER$更换用户名

注:修改oracle用户名需要sys用户,或者给操作用户操作user$表的权限。

SQL> grant select on user$ TO system;

SQL> grant update on user$ to system;

1、 查看用户的user#

SQL> show user

USER is “SYSTEM”

SQL> select user#,NAME from SYS.user$ WHERE NAME=’TEST’;

USER# NAME

———- ——————————

93 TEST

2、修改用户名

SQL> UPDATE USER$ SET NAME=’新用户名’ WHERE USER#=93;

已更新 1 行。

注:单引号中的新用户名一定要用大写,如果是小写,下面会提示找不到该用户。

3、提交完成

SQL> COMMIT;

4、修改系统检查点

SQL> ALTER SYSTEM CHECKPOINT;

5、修改新用户密码

SQL> ALTER USER 新用户名 IDENTIFIED BY 新密码;

6、如果提示新用户不存在,则刷新shared_pool

SQL> ALTER USER 新用户名 IDENTIFIED BY 新密码

*ERROR 位于第 1 行:ORA-01918: 用户’新用户’不存在

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

7、刷新shared_pool后重新修改用户密码

SQL> ALTER USER 新用户 IDENTIFIED BY 新密码;

8、测试连接

SQL> CONN 新用户/新密码@orcl;

9、查看新用户是否拥有原用户的对象

SQL> SELECT * FROM TAB;

五、mysql远程连接

1、改表法

可能是你的帐号不允许从远程登陆,只能在localhost。这个时候只要在localhost的那台电脑,登入mysql后,更改 “mysql” 数据库里的 “user” 表里的 “host” 项,从”localhost”改称”%”。代码如下:

mysql -u root –p vmware

mysql> use mysql;

mysql> update user set host = ‘%’ where user = ‘root’;

mysql> select host, user from user;

2、授权法

例如,你想myuser使用mypassword从任何主机连接到mysql服务器的话。

GRANT ALL PRIVILEGES ON *.* TO ‘myuser’@’%’IDENTIFIED BY ‘mypassword’ WITH GRANT OPTION;

如果你想允许用户myuser从ip为192.168.1.6的主机连接到mysql服务器,并使用mypassword作为密码。

GRANT ALL PRIVILEGES ON *.* TO ‘myuser’@’192.168.1.3’IDENTIFIED BY

‘mypassword’ WITH GRANT OPTION;

我用的第一个方法,刚开始发现不行,在网上查了一下,少执行一个语句 mysql>FLUSH RIVILEGES使修改生效,就可以了。

3、另外一种方法

在安装mysql的机器上运行:

1)进入MySQL服务器

d:\mysql\bin\>mysql -h localhost -u root

2)赋予任何主机访问数据的权限

mysql>GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’WITH GRANT OPTION

3)修改生效

mysql>FLUSH PRIVILEGES

4)退出MySQL服务器

mysql>EXIT

这样就可以在其它任何的主机上以root身份登录啦。

如果经过上面的操作,还不能解决问题,那可能就是服务器的安全设置问题,是不是ip安全策略或防火墙没有开启3306的例外。

4、安全模式修改用户密码

在用root登录mysql数据库的时候报错:

[root@mail mysql]# mysql -u root -p

Enter password:

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

这个错误一般都是root登录mysql的密码错误造成的(root登录mysql的密码和登录系统时的密码不是同一个密码,有时候可以不一样),使用下面的方法可以重新设定root登录mysql数据库的密码,该方法非常使用,有时候为了系统安全,一定要把root登录mysql和登录系统时的密码改成不一致:

[root@localhost mysql]# service mysqld stop

[root@localhost mysql]# mysqld_safe –user=mysql –skip-grant-tables –skip-networking &

然后可以不用密码直接进入系统:

[root@localhost mysql]# mysql -u root mysql

进入系统后可以直接修改root用户的密码:

mysql> UPDATE user SET Password=PASSWORD(‘newpassword’) where USER=’root’;

mysql> FLUSH PRIVILEGES;

mysql> quit

[root@localhost mysql]# service mysqld start

[root@localhost mysql]# mysql -uroot -p

Enter password: <输入新设的密码newpassword>

mysql>

启动mysql安全模式的命令参数:

skip-grant-tables:grant-tables,授权表。在启动mysql时不启动这个表,像忘了密码啥的,用这个模式启动很方便的。

skip-networking:不监听3306,说白了就是不启动mysql的网络服务。

user=mysql:这俺就不形容了。

六、linux下mysql卸载和安装

1、MySQL RPM版本的安装及卸载

1、前期准备

由于redhat Enterprise 5 中自带一个mysql的版本,如果在安装linux前未选择mysql的安装此步就可跳过,因为我当时安装了现在将其卸载,步骤如下:

  1. 查找已安装的myslq 版本

# rpm -qa|grep mysql

注意大小写,如果mysql 不行就换MySQL。在屏幕上将显示已安装的mysql包名如:mysql-5.0.22-2.1.0.1 ;

  1. 将搜索出的包名卸载

# rpm –e –nodeps mysql-5.0.22-2.1.0.1

nodeps表示强制删除

  1. 再次查找该包名

如果没有结果输出则表明已将该版本的mysql卸载了 。

  1. 准备安装资源
  2. perl-DBI-1.5.2-1.fc6.i386.rpm

该包为是安装mysql的依赖,没它mysql安装不起,查看是否安装,如果安装了就不需要了,可以通过 # rpm -qa | grep perl* 查看是否已经安装,该包在linux系统盘上可以找到,以下提供该包下载。

  1. MySQL-server-community-5.0.67-0.rhel5.rpm

MySQL-client-community-5.0.67-0.rhel5.rpm

可从http://dev.mysql.com/downloads/mysql/5.0.html 下载相应linux相应的版本。

  1. 安装文件上传到服务器

准备好相应的安装包后将其利用FTP上传到 linux系统路径下。

  1. 开始安装
  2. 先安装 perl-DBI-1.5.2-1.fc6.i386.rpm

#rpm -ivh perl-DBI-1.5.2-1.fc6.i386.rpm

查看是否安装成功rpm -qa|grep perl-DBI*

  1. 安装好perl,接着先安装 mysql server版

#rpm -ivh MySQL-server-community-5.0.67-0.rhel5.rpm

查看是否安装成功rpm -qa|grep MySQL ,查看该进程是否启动ps -ef|grep MySQL ,由于安装好server版后就已经启动了。

  1. 安装好server版,开始接着安装 client版:

# rpm -ivh MySQL-client-community-5.0.67-0.rhel5.rpm

查看是否安装成功 #rpm -qa | MySQL。

  1. 好了到此就已经安装完毕,测试是否可以运行:

# mysql

出现 mysql> 命令符说明安装成功,默认用户为root没有密码所以可以直接进入。

2、MySQL二进制版本的安装及卸载

二进制版本的mysql是已经编译好的,无需configure,make make install 等步骤,只需配置一下即可使用,卸载也方便,直接删除即可;现在以mysql-standard-4.1.13-pc-linux-gnu-i686.tar.gz 版本做介绍 :

  1.   改变该文件为可执行权限

# chmod 755  mysql-standard-4.1.13-pc-linux-gnu-i686.tar.gz

  1. 将解压后生成的目录,复制到/usr/local/下并改名为mysql

# tar -xvzf  mysql-standard-4.1.13-pc-linux-gnu-i686.tar.gz

  1.   建立mysql组

# groupadd mysql

  1.   建立mysql用户并且加入到mysql组中

# useradd mysql -g mysql

  1.   创建mysql配置文件

# cp /usr/local/mysql/support-files/my-medium.cnf /etc/my.cnf

在 support-files目录下有4个模版文件,我们选择其中一个座位Mysql的配置文件,覆盖/etc/my.cnf(系统默认的配置,其中设置了性能参数和Mysql的一些路径参数)。

  1. 进入mysql目录

# cd /usr/local/mysql

初试化表并且规定用mysql用户来访问

# ./scripts/mysql_install_db –user=mysql

初始化表以后就开始给mysql和root用户设定访问权限。

  1. 设定mysql用户访问权限

# chown -R mysql data

设定mysql用户能访问/usr/local/mysql/data ,里面存的是mysql的数据库文件.这个目录是在/etc/my.cnf中有配置,mysql_install_db时产生。

# chown -R mysql data/.

设定mysql用户能访问/usr/local/mysql/data/mysql下的所有文件。

# chgrp -R mysql .

设定mysql组能够访问/usr/local/mysql。

# /usr/local/mysql/bin/mysqld_safe –user=mysql &

  1. 进入 bin目录

cd /usr/local/mysql/bin

  1. 运行mysq命令

#mysql

如果没有没有启动 或出现 Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’。

解决方法:

#cd /usr/local/mysql/support-files

启动即可既可在生成/tmp/mysql.sock

#./mysql.server start

如果 /tmp/mysql.sock 无该文件存在,且mysql是处于开启的状态。

解决方法:

#netstat -an | grep 3306   //查看 3306端口

#ps -ef | grep mysql  // 查找 mysql 进程

# kill -9  进程号    //强制删除mysql的进程号

#./mysql.server start   //启动即可 既可在生成 /tmp/mysql.sock

如果在任意路径下如数mysql命令得到的无该命令咋办呢?

解决方法:

修改 /etc/profile 文件,在文件中加入

PATH=$PATH:/usr/local/mysql/bin/

export PATH

保存即可退出执行 source /etc/profile。以上操作完既可以在任意目录执行# mysql 命令。

如果想执行 service mysql start or restart stop 命令咋办的? 在不能老#./mysql.server start

解决方法:

将 mysql.server复制一份到/etc/rc.d/init.d下并改名 mysql 或 建个连接文件,假设当前目录为/etc/rc.d/init.d

如:#cp /usr/local/mysql/support-files/mysql.server mysql(复制)

或:#ln -s /usr/local/mysql/support-files/mysql.server mysql (建立连接文件)

完成以上操作即可  执行 service mysql start (or restart stop)

用如下命令修改MYSQL密码

# /usr/local/mysql/bin/mysqladmin -u root password yourpassword

默认安装密码为空,为了安全你必须马上修改。

# chmod 700 /etc/init.d/mysql

# chkconfig –add mysqld

# chkconfig –level 345 mysql on

copy编译目录的一个脚本设置使mysql每次启动都能自动运行。

# service mysql start

# netstat -atln

//启动mysql服务

//查看3306端口是否打开。要注意在防火墙中开放该端口。

可以用telnet  localhost  3306来测试一下,如果有反映,那就表明安装成功了。

七、linux下修改mysql字符集

1.查找MySQL的cnf文件的位置
[root@localhost ~]# find / -iname ‘*.cnf’ -print

/usr/share/mysql/my-innodb-heavy-4G.cnf

/usr/share/mysql/my-large.cnf

/usr/share/mysql/my-small.cnf

/usr/share/mysql/my-medium.cnf

/usr/share/mysql/my-huge.cnf

/usr/share/texmf/web2c/texmf.cnf

/usr/share/texmf/web2c/mktex.cnf

/usr/share/texmf/web2c/fmtutil.cnf

/usr/share/texmf/tex/xmltex/xmltexfmtutil.cnf

/usr/share/texmf/tex/jadetex/jadefmtutil.cnf

/usr/share/doc/MySQL-server-community-5.1.22/my-innodb-heavy-4G.cnf

/usr/share/doc/MySQL-server-community-5.1.22/my-large.cnf

/usr/share/doc/MySQL-server-community-5.1.22/my-small.cnf

/usr/share/doc/MySQL-server-community-5.1.22/my-medium.cnf

/usr/share/doc/MySQL-server-community-5.1.22/my-huge.cnf

2.新建my.cnf文件
拷贝 small.cnf、my-medium.cnf、my-huge.cnf、my-innodb-heavy-4G.cnf其中的一个到/etc下,命名为my.cnf。

[root@localhost ~]# cp /usr/share/mysql/my-medium.cnf /etc/my.cnf

3.修改my.cnf
[root@localhost ~]# vi /etc/my.cnf

在[client]下添加

default-character-set=utf8

在[mysqld]下添加

default-character-set=utf8

4.重新启动MySQL
[root@localhost ~]# /etc/rc.d/init.d/mysql restart

Shutting down MySQL                                         [ 确定 ]

Starting MySQL.                                             [ 确定 ]

[root@localhost ~]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.1.22-rc-community-log MySQL Community Edition (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

5.查看字符集设置
mysql> show variables like ‘collation_%’;

+———————-+—————–+

| Variable_name         | Value            |

+———————-+—————–+

| collation_connection | utf8_general_ci |

| collation_database    | utf8_general_ci |

| collation_server      | utf8_general_ci |

+———————-+—————–+

3 rows in set (0.02 sec)

mysql> show variables like ‘character_set_%’;

+————————–+—————————-+

| Variable_name             | Value                       |

+————————–+—————————-+

| character_set_client      | utf8                        |

| character_set_connection | utf8                        |

| character_set_database    | utf8                        |

| character_set_filesystem | binary                      |

| character_set_results     | utf8                        |

| character_set_server      | utf8                        |

| character_set_system      | utf8                        |

| character_sets_dir        | /usr/share/mysql/charsets/ |

+————————–+—————————-+

8 rows in set (0.02 sec)
6.其他的一些设置方法
修改数据库的字符集

mysql>use mydb

mysql>alter database mydb character set utf-8;

创建数据库指定数据库的字符集

mysql>create database mydb character set utf-8;

通过配置文件修改:

修改/var/lib/mysql/mydb/db.opt

default-character-set=latin1

default-collation=latin1_swedish_ci

default-character-set=utf8

default-collation=utf8_general_ci

重起MySQL:

[root@localhost ~]# /etc/rc.d/init.d/mysql restart

通过MySQL命令行修改:

mysql> set character_set_client=utf8;

Query OK, 0 rows affected (0.00 sec)

mysql> set character_set_connection=utf8;

Query OK, 0 rows affected (0.00 sec)

mysql> set character_set_database=utf8;

Query OK, 0 rows affected (0.00 sec)

mysql> set character_set_results=utf8;

Query OK, 0 rows affected (0.00 sec)

mysql> set character_set_server=utf8;

Query OK, 0 rows affected (0.00 sec)

mysql> set character_set_system=utf8;

Query OK, 0 rows affected (0.01 sec)

mysql> set collation_connection=utf8;

Query OK, 0 rows affected (0.01 sec)

mysql> set collation_database=utf8;

Query OK, 0 rows affected (0.01 sec)

mysql> set collation_server=utf8;

Query OK, 0 rows affected (0.01 sec)

查看:

mysql> show variables like ‘character_set_%’;

+————————–+—————————-+

| Variable_name             | Value                       |

+————————–+—————————-+

| character_set_client      | utf8                        |

| character_set_connection | utf8                        |

| character_set_database    | utf8                        |

| character_set_filesystem | binary                      |

| character_set_results     | utf8                        |

| character_set_server      | utf8                        |

| character_set_system      | utf8                        |

| character_sets_dir        | /usr/share/mysql/charsets/ |

+————————–+—————————-+

8 rows in set (0.03 sec)

mysql> show variables like ‘collation_%’;

+———————-+—————–+

| Variable_name         | Value            |

+———————-+—————–+

| collation_connection | utf8_general_ci |

| collation_database    | utf8_general_ci |

| collation_server      | utf8_general_ci |

+———————-+—————–+

3 rows in set (0.04 sec)

7.MYSQL字符集问题
MySQL的字符集支持(Character Set Support)有两个方面:字符集(Character set)和排序方式(Collation)。对于字符集的支持细化到四个层次:

服务器(server),数据库(database),数据表(table)和连接(connection)。

1.MySQL默认字符集

MySQL对于字符集的指定可以细化到一个数据库,一张表,一列,应该用什么字符集。

但是,传统的程序在创建数据库和数据表时并没有使用那么复杂的配置,它们用的是默认的配置,那么,默认的配置从何而来呢?

(1)编译MySQL 时,指定了一个默认的字符集,这个字符集是 latin1;

(2)安装MySQL 时,可以在配置文件 (my.ini) 中指定一个默认的的字符集,如果没指定,这个值继承自编译时指定的;

(3)启动mysqld 时,可以在命令行参数中指定一个默认的的字符集,如果没指定,这个值继承自配置文件中的配置,此时 character_set_server 被设定为这个默认的字符集;

(4)当创建一个新的数据库时,除非明确指定,这个数据库的字符集被缺省设定为character_set_server;

(5)当选定了一个数据库时,character_set_database 被设定为这个数据库默认的字符集;

(6)在这个数据库里创建一张表时,表默认的字符集被设定为 character_set_database,也就是这个数据库默认的字符集;

(7)当在表内设置一栏时,除非明确指定,否则此栏缺省的字符集就是表默认的字符集;

简单的总结一下,如果什么地方都不修改,那么所有的数据库的所有表的所有栏位的都用

latin1 存储,不过我们如果安装 MySQL,一般都会选择多语言支持,也就是说,安装程序会自动在配置文件中把

default_character_set 设置为 UTF-8,这保证了缺省情况下,所有的数据库的所有表的所有栏位的都用 UTF-8 存储。

2.查看默认字符集

默认情况下,mysql的字符集是latin1(ISO_8859_1)。通常,查看系统的字符集和排序方式的设定可以通过下面的两条命令:

mysql> SHOW VARIABLES LIKE ‘character%’;

+————————–+———————————+

| Variable_name             | Value                            |

+————————–+———————————+

| character_set_client      | latin1                           |

| character_set_connection | latin1                           |

| character_set_database    | latin1                           |

| character_set_filesystem | binary                      |

| character_set_results     | latin1                           |

| character_set_server      | latin1                           |

| character_set_system     | utf8                             |

| character_sets_dir        | D:”mysql-5.0.37″share”charsets” |

+————————–+———————————+

mysql> SHOW VARIABLES LIKE ‘collation_%’;

+———————-+—————–+

| Variable_name         | Value            |

+———————-+—————–+

| collation_connection | utf8_general_ci |

| collation_database    | utf8_general_ci |

| collation_server      | utf8_general_ci |

+———————-+—————–+

3.修改默认字符集

(1) 最简单的修改方法,就是修改mysql的my.ini文件中的字符集键值,

如     default-character-set = utf8

character_set_server = utf8

修改完后,重启mysql的服务,service mysql restart,查看字符集,发现数据库编码均已改成utf8:

mysql> SHOW VARIABLES LIKE ‘character%’;

+————————–+———————————+

| Variable_name             | Value                            |

+————————–+———————————+

| character_set_client      | utf8                             |

| character_set_connection | utf8                             |

| character_set_database    | utf8                             |

| character_set_filesystem | binary                           |

| character_set_results     | utf8                             |

| character_set_server      | utf8                             |

| character_set_system      | utf8                             |

| character_sets_dir        | D:”mysql-5.0.37″share”charsets” |

+————————–+———————————+

(2) 还有一种修改字符集的方法,就是使用mysql的命令

mysql> SET character_set_client = utf8 ;

八、mysql查看数据库结构

1、查看表信息
mysql> desc 表名;

mysql> show columns from 表名;

mysql> describe 表名;

mysql> show create table 表名;

mysql> use information_schema

mysql> select * from columns where table_name=’表名’;

2、查看数据库
mysql> show databases;

mysql> use 数据库名;

mysql> show tables;

3、alter table操作
原有一unique索引AK_PAS_Name(PAC_Name)在表tb_webparamcounter中,执行以下sql修改索引

mysql> alter table tb_webparamcounter drop index AK_PAS_Name;

mysql> alter table tb_webparamcounter add UNIQUE AK_PAS_Name(PC_ID,PAC_Name);

若发现索引的逻辑不对,还需要再加一个字段进去,执行:

mysql> alter table tb_webparamcounter drop index AK_PAS_Name;

mysql> alter table tb_webparamcounter add UNIQUE AK_PAS_Name(PC_ID,PAC_Name,PAC_Value);

注意:这时的PC_ID,PAC_Name,PAC_Value三个字段不是FOREIGN KEY,否则必需先drop FOREIGN KEY,再重做上一步才行。

4、查看存储过程
mysql> show procedure status;

九、linux下mysql命令集

1.linux下启动mysql
[root@localhost ~]# mysqladmin start

/ect/init.d/mysql start (前面为mysql的安装路径)

2.linux下重启mysql
[root@localhost ~]# mysqladmin restart

/ect/init.d/mysql restart (前面为mysql的安装路径)

3.linux下关闭mysql的命令:
[root@localhost ~]# mysqladmin shutdown

/ect/init.d/mysql shutdown (前面为mysql的安装路径)

4.连接本机上的mysql
进入目录mysql\bin,再键入命令mysql -uroot -p, 回车后提示输入密码。

退出mysql命令:exit(回车)

5.修改mysql密码
mysqladmin -u用户名 -p旧密码 password 新密码,或进入mysql命令行SET PASSWORD FOR root=PASSWORD(“root”);

6.增加新用户
注意:mysql环境中的命令后面都带一个分号作为命令结束符。

grant select on 数据库.* to 用户名@登录主机 identified by “密码”。如增加一个用户test密码为123,让他可以在任何主机上登录, 并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入mysql,然后键入以下命令:

grant select,insert,update,delete on *.* to test Identified by “123”;

7.有关mysql数据库方面的操作
必须首先登录到mysql中,有关操作都是在mysql的提示符下进行,而且每个命令以分号结束

1、显示数据库列表。

mysql> show databases;

2、显示库中的数据表:

mysql> use mysql; //打开库

mysql> show tables;

3、显示数据表的结构:

mysql> describe 表名;

4、建库:

mysql> create database 库名;

5、建表:

mysql> use 库名;

mysql> create table 表名(字段设定列表);

6、删库和删表:

mysql> drop database 库名;

mysql> drop table 表名;

7、将表中记录清空:

mysql> delete from 表名;

8、显示表中的记录:

mysql> select * from 表名;

9、编码的修改

如果要改变整个mysql的编码格式:

启动mysql的时候,mysqld_safe命令行加入

–default-character-set=gbk

如果要改变某个库的编码格式:在mysql提示符后输入命令

alter database db_name default character set gbk;

8.数据的导入导出
1、文本数据转到数据库中

文本数据应符合的格式:字段数据之间用tab键隔开,null值用来代替。例:

1 name duty 2006-11-23

数据传入命令 load data local infile “文件名” into table 表名;

2、导出数据库和表

mysqldump –opt news > news.sql(将数据库news中的所有表备份到news.sql文件,news.sql是一个文本文件,文件名任取。)

mysqldump –opt news author article > author.article.sql(将数据库news中的author表和article表备份到author.article.sql文件, author.article.sql是一个文本文件,文件名任取。)

mysqldump –databases db1 db2 > news.sql(将数据库dbl和db2备份到news.sql文件,news.sql是一个文本文件,文件名任取。)

mysqldump -h host -u user -p pass –databases dbname > file.dump

就是把host上的以名字user,口令pass的数据库dbname导入到文件file.dump中

mysqldump –all-databases > all-databases.sql(将所有数据库备份到all-databases.sql文件,all-databases.sql是一个文本文件,文件名任取。)

3、导入数据

mysql < all-databases.sql(导入数据库)

mysql>source news.sql;(在mysql命令下执行,可导入表)

9.连接MySQL
格式: mysql -h主机地址 -u用户名 -p用户密码

1、例1:连接到本机上的MYSQL。

首先在打开DOS窗口,然后进入目录 mysqlbin,再键入命令mysql -uroot -p,回车后提示你输密码,如果刚安装好MYSQL,超级用户root是没有密码的,故直接回车即可进入到MYSQL中了,MYSQL的提示符是: mysql>。

2、例2:连接到远程主机上的MYSQL。假设远程主机的IP为:110.110.110.110,用户名为root,密码为abcd123。则键入以下命令:

mysql -h110.110.110.110 -uroot -pabcd123

(注:u与root可以不用加空格,其它也一样)

3、退出MYSQL命令: exit (回车)。

11.修改密码
格式:mysqladmin -u用户名 -p旧密码 password 新密码

1、例1:给root加个密码ab12。首先在DOS下进入目录mysqlbin,然后键入以下命令:

mysqladmin -uroot -password ab12

注:因为开始时root没有密码,所以-p旧密码一项就可以省略了。

2、例2:再将root的密码改为djg345。

mysqladmin -uroot -pab12 password djg345

12.增加新用户
注意:和上面不同,下面的因为是MySQL环境中的命令,所以后面都带一个分号作为命令结束符。

格式:grant select on 数据库.* to 用户名@登录主机 identified by \”密码\”

例1、增加一个用户test1密码为abc,让他可以在任何主机上登录,并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入MySQL,然后键入以下命令:

grant select,insert,update,

delete on *.* to test1@\”%\” Identified by \”abc\”;

但例1增加的用户是十分危险的,你想如某个人知道test1的密码,那么他就可以在internet上的任何一台电脑上登录你的MySQL数据库并对你的数据可以为所欲为了,解决办法见例2。

例2、增加一个用户test2密码为abc,让他只可以在localhost上登录,并可以对数据库mydb进行查询、插入、修改、删除的操作(localhost指本地主机,即MySQL数据库所在的那台主机),这样用户即使用知道test2的密码,他也无法从internet上直接访问数据 库,只能通过MySQL主机上的web页来访问。

grant select,insert,update,

delete on mydb.* to test2@localhost identified by \”abc\”;

如果你不想test2有密码,可以再打一个命令将密码消掉。

grant select,insert,update,delete on mydb.* to test2@localhost identified by \”\”;

十、linux mysql常见问题

1、Access denied for user ‘root’@’localhost’
系统是ubuntu6.06,最近新装好的mysql在进入mysql工具时,总是有错误提示:

# mysql -uroot -p

Enter password:

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

使用网上介绍的方法修改root用户的密码:

# mysqladmin -uroot -p password ‘newpassword’

Enter password:

mysqladmin: connect to server at ‘localhost’ failed

error: ‘Access denied for user ‘root’@’localhost’ (using password: YES)’

现在终于被我找到了解决方法,如下(请先测试方法三):

方法一:

# /etc/init.d/mysql stop

# mysqld_safe –user=mysql –skip-grant-tables –skip-networking &

# mysql -u root mysql

mysql> UPDATE user SET Password=PASSWORD(‘newpassword’) where USER=’root’;

mysql> FLUSH PRIVILEGES;

mysql> quit

# /etc/init.d/mysql restart

# mysql -uroot -p

Enter password: <输入新设的密码newpassword>

mysql>

方法二:

直接使用/etc/mysql/debian.cnf文件中[client]节提供的用户名和密码:

# mysql -udebian-sys-maint -p

Enter password: <输入[client]节的密码>

mysql> UPDATE user SET Password=PASSWORD(‘newpassword’) where USER=’root’;

mysql> FLUSH PRIVILEGES;

mysql> quit
# mysql -uroot -p

Enter password: <输入新设的密码newpassword>

mysql>

方法三:

# mysql -uroot -p

Enter password: <输入/etc/mysql/debian.cnf文件中[client]节提供的密码>

至此,问题解决!

2、sqlplus连接mysql
无法查询null字段,在$ORACLE_HOME/hs/admin/initmysql.ora文件中加入:HS_FDS_SQLLEN_INTERPRETATION=32即可。

不能直接访问mysql的int字段。在$ORACLE_HOME/hs/admin/initmysql.ora文件中加入:HS_FDS_FETCH_ROWS=1即可。

mysql 中整型转字符串

将linkurl字段的值修改为’show.php?itemid=’ 加上itemid的值,sql语句如下:

 

 

update  `buy` set  `linkurl` = concat('show.php?itemid=', cast(`itemid` as char(20)))   WHERE 1

 

 

 

Cast函数和操作符

  • BINARY

BINARY操作符将后面的字符串抛给一个二进制字符串。这是一种简单的方式来促使逐字节而不是逐字符的进行列比较。这使得比较区分大小写,即使该列不被定义为 BINARY或 BLOB。BINARY也会产生结尾空白,从而更加显眼。

mysql> SELECT ‘a’ = ‘A’;

-> 1

mysql> SELECT BINARY ‘a’ = ‘A’;

-> 0

mysql> SELECT ‘a’ = ‘a ‘;

-> 1

mysql> SELECT BINARY ‘a’ = ‘a ‘;

-> 0

BINARY影响整个比较;它可以在任何操作数前被给定,而产生相同的结果。

BINARY str 是CAST(str AS BINARY)的缩略形式。

注意,在一些语境中,假如你将一个编入索引的列派给BINARY, MySQL 将不能有效使用这个索引。

假如你想要将一个 BLOB值或其它二进制字符串进行区分大小写的比较,你可利用二进制字符串没有字符集这一事实实现这个目的,这样就不会有文书夹的概念。为执行一个区分大小写的比较,可使用  CONVERT()函数将一个字符串值转化为一个不区分大小写的字符集。其结果为一个非二进制字符串,因此 LIKE 操作也不会区分大小写:

SELECT ‘A’ LIKE CONVERT(blob_col USING latin1) FROM tbl_name;

若要使用一个不同的字符集, 替换其在上述语句中的latin1名。

CONVERT()一般可用于比较出现在不同字符集中的字符串。

  • CAST(expr AS type), CONVERT(expr,type) , CONVERT(expr USING transcoding_name)

CAST() 和CONVERT() 函数可用来获取一个类型的值,并产生另一个类型的值。

这个类型 可以是以下值其中的 一个:

  • BINARY[(N)]
  • CHAR[(N)]
  • DATE
  • DATETIME
  • DECIMAL
  • SIGNED [INTEGER]
  • TIME
  • UNSIGNED [INTEGER]

BINARY 产生一个二进制字符串。关于它怎样影响比较结果的说明见本章中 BINARY操作符项。

假如给定了随意长度N,则 BINARY[N] 使 cast使用该参数的不多于 N 个字节。同样的,CHAR[N]会使 cast 使用该参数的不多于N 个字符。

CAST() and CONVERT(… USING …) 是标准 SQL语法。CONVERT()的非USING 格式是ofis ODBC语法。

带有USING的CONVERT() 被用来在不同的字符集之间转化数据。在 MySQL中, 自动译码名和相应的字符集名称相同。例如。 这个语句将服务器的默认字符集中的字符串 ‘abc’转化为utf8字符集中相应的字符串:

SELECT CONVERT(‘abc’ USING utf8);

当你想要在一个CREATE … SELECT 语句中创建一个特殊类型的列,则cast函数会很有用:

CREATE TABLE new_table SELECT CAST(‘2000-01-01’ AS DATE);

该函数也用于ENUM 列按词法顺序的排序。通常ENUM列的排序在使用内部数值时发生。将这些值按照词法顺序派给 CHAR 结果:

SELECT enum_col FROM tbl_name ORDER BY CAST(enum_col AS CHAR);

CAST(str AS BINARY)和BINARY str相同 CAST(expr AS CHAR) 将表达式视为一个带有默认字符集的字符串。

若用于一个诸如 CONCAT(‘Date: ‘,CAST(NOW() AS DATE))这样的比较复杂的表达式的一部分,CAST()也会改变结果。

你不应在不同的格式中使用 CAST() 来析取数据,但可以使用诸如LEFT() 或 EXTRACT() 的样的字符串函数来代替。

若要在数值语境中将一个字符串派给一个数值, 通常情况下,除了将字符串值作为数字使用外,你不需要做任何事:

mysql> SELECT 1+’1′;

-> 2

若要在一个字符串语境中使用一个数字,该数字会被自动转化为一个BINARY 字符串。

mysql> SELECT CONCAT(‘hello you ‘,2);

-> ‘hello you 2’

MySQL 支持带符号和无符号的64比特值的运算。若你正在使用数字操作符  (如 +) 而其中一个操作数为无符号整数,则结果为无符号。可使用SIGNED 和UNSIGNED cast 操作符来覆盖它。将运算分别派给带符号或无符号64比特整数。

mysql> SELECT CAST(1-2 AS UNSIGNED)

-> 18446744073709551615

mysql> SELECT CAST(CAST(1-2 AS UNSIGNED) AS SIGNED);

-> -1

注意,假如任意一个操作数为一个浮点值,则结果为一个浮点值, 且不会受到上述规则影响 (关于这一点, DECIMAL 列值被视为浮点值)。

mysql> SELECT CAST(1 AS UNSIGNED) – 2.0;

-> -1.0

若你在一个算术运算中使用了一个字符串,它会被转化为一个浮点数。

mysql 一个较特殊的问题:You can’t specify target table ‘wms_cabinet_form’ for update in FROM clause

今天在写 mysql 遇到一个比较特殊的问题。
mysql 语句如下:

update wms_cabinet_form set cabf_enabled=0

where cabf_id in (

SELECT wms_cabinet_form.cabf_id FROM wms_cabinet_form

Inner Join wms_cabinet ON wms_cabinet_form.cabf_cab_id = wms_cabinet.cab_id

Inner Join wms_cabinet_row ON wms_cabinet.cab_row_id =wms_cabinet_row.row_id

where wms_cabinet_row.row_site_id=27 and wms_cabinet_form.cabf_enabled=1)
运行时提出如下提示: You can’t specify target table ‘wms_cabinet_form’ for update in FROM clause

运行 in 里面的 select 字句:

 

SELECT wms_cabinet_form.cabf_id FROM wms_cabinet_form

Inner Join wms_cabinet ON wms_cabinet_form.cabf_cab_id = wms_cabinet.cab_id

Inner Join wms_cabinet_row ON wms_cabinet.cab_row_id =wms_cabinet_row.row_id

where wms_cabinet_row.row_site_id=27 and wms_cabinet_form.cabf_enabled=1

 

可以正确 select 正确结果。再把结果直接写到 in 里面,改后语句如下:

update wms_cabinet_form set cabf_enabled=0 where cabf_id in (‘113′,’114′,’115’),再运行可以正确执行更新。

到这一步开始想不明白,为什么用 select 子句运行会出错呢?以前在 mssql 这种写法是很常见的。
没办法了,唯有动用 baidu。找到两条记录。

原来原因是:mysql中不能这么用。 (等待mysql升级吧)。那串英文错误提示就是说,不能先select出同一表中的某些值,

再update这个表(在同一语句中)。 也找到替代方案,重写改写了 sql 。

 

改写后的 sql 如下所示,大家仔细区别一下。

 

update wms_cabinet_form set cabf_enabled=0 where cabf_id in (

SELECT a.cabf_id FROM (select tmp.* from wms_cabinet_form tmp) a

Inner Join wms_cabinet b ON a.cabf_cab_id = b.cab_id

Inner Join wms_cabinet_row c ON b.cab_row_id = c.row_id

where c.row_site_id=29 and a.cabf_enabled=1)

 

重点在 SELECT a.cabf_id FROM (select tmp.* from wms_cabinet_form tmp) a ,我 select tmp.* from wms_cabinet_form tmp 作为子集,

然后再 select a.cabf_id FROM 子集,这样就不会 select 和 update 都是同一个表。致此问题得到完美解决。

 

 
update bs_budgetitem set haschild = 1 where id in(

SELECT a.id FROM (select tmp.* from bs_budgetitem tmp) a Inner Join (select tmp1.* from bs_budgetitem tmp1) b ON b.parentid = a.id

)