标签归档:mysql

MYSQL查询重复记录的方法

MYSQL查询重复记录的方法很多,下面就为您介绍几种最常用的MYSQL查询重复记录的方法,希望对您学习MYSQL查询重复记录方面能有所帮助。

1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断

  1. select * from people
  2. where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录

  1. delete from people
  2. where peopleId in (select peopleId from people group by peopleId   having count(peopleId) > 1)
  3. and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)

3、查找表中多余的重复记录(多个字段)

  1. select * from vitae a
  2. where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录

  1. delete from vitae a
  2. where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
  3. and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录

  1. select * from vitae a
  2. where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
  3. and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

MYSQL性能优化的最佳20+条经验

今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显。关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事情。当我们去设计数据库表结构,对操作数据库时(尤其是查表时的SQL语句),我们都需要注意数据操作的性能。这里,我们不会讲过多的SQL语句的优化,而只是针对MySQL这一Web应用最多的数据库。希望下面的这些优化技巧对你有用。

1. 为查询缓存优化你的查询

大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查询结果会被放到一个缓存中,这样,后续的相同的查询就不用操作表而直接访问缓存结果了。

这里最主要的问题是,对于程序员来说,这个事情是很容易被忽略的。因为,我们某些查询语句会让MySQL不使用缓存。请看下面的示例:

1
2
3
4
5
6
// 查询缓存不开启
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
// 开启查询缓存
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

上面两条SQL语句的差别就是 CURDATE() ,MySQL的查询缓存对这个函数不起作用。所以,像 NOW() 和 RAND() 或是其它的诸如此类的SQL函数都不会开启查询缓存,因为这些函数的返回是会不定的易变的。所以,你所需要的就是用一个变量来代替MySQL的函数,从而开启缓存。

2. EXPLAIN 你的 SELECT 查询

使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。

EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的,你的数据表是如何被搜索和排序的……等等,等等。

挑一个你的SELECT语句(推荐挑选那个最复杂的,有多表联接的),把关键字EXPLAIN加到前面。你可以使用phpmyadmin来做这个事。然后,你会看到一张表格。下面的这个示例中,我们忘记加上了group_id索引,并且有表联接:

当我们为 group_id 字段加上索引后:

我们可以看到,前一个结果显示搜索了 7883 行,而后一个只是搜索了两个表的 9 和 16 行。查看rows列可以让我们找到潜在的性能问题。

3. 当只要一行数据时使用 LIMIT 1

当你查询表的有些时候,你已经知道结果只会有一条结果,但因为你可能需要去fetch游标,或是你也许会去检查返回的记录数。

在这种情况下,加上 LIMIT 1 可以增加性能。这样一样,MySQL数据库引擎会在找到一条数据后停止搜索,而不是继续往后查少下一条符合记录的数据。

下面的示例,只是为了找一下是否有“中国”的用户,很明显,后面的会比前面的更有效率。(请注意,第一条中是Select *,第二条是Select 1)

1
2
3
4
5
6
7
8
9
10
11
// 没有效率的:
$r = mysql_query("SELECT * FROM user WHERE country = 'China'");
if (mysql_num_rows($r) > 0) {
    // ...
}
// 有效率的:
$r = mysql_query("SELECT 1 FROM user WHERE country = 'China' LIMIT 1");
if (mysql_num_rows($r) > 0) {
    // ...
}

4. 为搜索字段建索引

索引并不一定就是给主键或是唯一的字段。如果在你的表中,有某个字段你总要会经常用来做搜索,那么,请为其建立索引吧。

从上图你可以看到那个搜索字串 “last_name LIKE ‘a%’”,一个是建了索引,一个是没有索引,性能差了4倍左右。

另外,你应该也需要知道什么样的搜索是不能使用正常的索引的。例如,当你需要在一篇大的文章中搜索一个词时,如: “WHERE post_content LIKE ‘%apple%’”,索引可能是没有意义的。你可能需要使用MySQL全文索引 或是自己做一个索引(比如说:搜索关键词或是Tag什么的)

5. 在Join表的时候使用相当类型的例,并将其索引

如果你的应用程序有很多 JOIN 查询,你应该确认两个表中Join的字段是被建过索引的。这样,MySQL内部会启动为你优化Join的SQL语句的机制。

而且,这些被用来Join的字段,应该是相同的类型的。例如:如果你要把 DECIMAL 字段和一个 INT 字段Join在一起,MySQL就无法使用它们的索引。对于那些STRING类型,还需要有相同的字符集才行。(两个表的字符集有可能不一样)

1
2
3
4
5
6
// 在state中查找company
$r = mysql_query("SELECT company_name FROM users
    LEFT JOIN companies ON (users.state = companies.state)
    WHERE users.id = $user_id");
// 两个 state 字段应该是被建过索引的,而且应该是相当的类型,相同的字符集。

6. 千万不要 ORDER BY RAND()

想打乱返回的数据行?随机挑一个数据?真不知道谁发明了这种用法,但很多新手很喜欢这样用。但你确不了解这样做有多么可怕的性能问题。

如果你真的想把返回的数据行打乱了,你有N种方法可以达到这个目的。这样使用只让你的数据库的性能呈指数级的下降。这里的问题是:MySQL会不得不去执行RAND()函数(很耗CPU时间),而且这是为了每一行记录去记行,然后再对其排序。就算是你用了Limit 1也无济于事(因为要排序)

下面的示例是随机挑一条记录

1
2
3
4
5
6
7
8
9
// 千万不要这样做:
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");
// 这要会更好:
$r = mysql_query("SELECT count(*) FROM user");
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] - 1);
$r = mysql_query("SELECT username FROM user LIMIT $rand, 1");

7. 避免 SELECT *

从数据库里读出越多的数据,那么查询就会变得越慢。并且,如果你的数据库服务器和WEB服务器是两台独立的服务器的话,这还会增加网络传输的负载。

所以,你应该养成一个需要什么就取什么的好的习惯。

1
2
3
4
5
6
7
8
9
// 不推荐
$r = mysql_query("SELECT * FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";
// 推荐
$r = mysql_query("SELECT username FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";

8. 永远为每张表设置一个ID

我们应该为数据库里的每张表都设置一个ID做为其主键,而且最好的是一个INT型的(推荐使用UNSIGNED),并设置上自动增加的AUTO_INCREMENT标志。

就算是你 users 表有一个主键叫 “email”的字段,你也别让它成为主键。使用 VARCHAR 类型来当主键会使用得性能下降。另外,在你的程序中,你应该使用表的ID来构造你的数据结构。

而且,在MySQL数据引擎下,还有一些操作需要使用主键,在这些情况下,主键的性能和设置变得非常重要,比如,集群,分区……

在这里,只有一个情况是例外,那就是“关联表”的“外键”,也就是说,这个表的主键,通过若干个别的表的主键构成。我们把这个情况叫做“外键”。比如:有一个“学生表”有学生的ID,有一个“课程表”有课程ID,那么,“成绩表”就是“关联表”了,其关联了学生表和课程表,在成绩表中,学生ID和课程ID叫“外键”其共同组成主键。

9. 使用 ENUM 而不是 VARCHAR

ENUM 类型是非常快和紧凑的。在实际上,其保存的是 TINYINT,但其外表上显示为字符串。这样一来,用这个字段来做一些选项列表变得相当的完美。

如果你有一个字段,比如“性别”,“国家”,“民族”,“状态”或“部门”,你知道这些字段的取值是有限而且固定的,那么,你应该使用 ENUM 而不是 VARCHAR。

MySQL也有一个“建议”(见第十条)告诉你怎么去重新组织你的表结构。当你有一个 VARCHAR 字段时,这个建议会告诉你把其改成 ENUM 类型。使用 PROCEDURE ANALYSE() 你可以得到相关的建议。

10. 从 PROCEDURE ANALYSE() 取得建议

PROCEDURE ANALYSE() 会让 MySQL 帮你去分析你的字段和其实际的数据,并会给你一些有用的建议。只有表中有实际的数据,这些建议才会变得有用,因为要做一些大的决定是需要有数据作为基础的。

例如,如果你创建了一个 INT 字段作为你的主键,然而并没有太多的数据,那么,PROCEDURE ANALYSE()会建议你把这个字段的类型改成 MEDIUMINT 。或是你使用了一个 VARCHAR 字段,因为数据不多,你可能会得到一个让你把它改成 ENUM 的建议。这些建议,都是可能因为数据不够多,所以决策做得就不够准。

在phpmyadmin里,你可以在查看表时,点击 “Propose table structure” 来查看这些建议

一定要注意,这些只是建议,只有当你的表里的数据越来越多时,这些建议才会变得准确。一定要记住,你才是最终做决定的人。

11. 尽可能的使用 NOT NULL

除非你有一个很特别的原因去使用 NULL 值,你应该总是让你的字段保持 NOT NULL。这看起来好像有点争议,请往下看。

首先,问问你自己“Empty”和“NULL”有多大的区别(如果是INT,那就是0和NULL)?如果你觉得它们之间没有什么区别,那么你就不要使用NULL。(你知道吗?在 Oracle 里,NULL 和 Empty 的字符串是一样的!)

不要以为 NULL 不需要空间,其需要额外的空间,并且,在你进行比较的时候,你的程序会更复杂。 当然,这里并不是说你就不能使用NULL了,现实情况是很复杂的,依然会有些情况下,你需要使用NULL值。

下面摘自MySQL自己的文档:

“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”

12. Prepared Statements

Prepared Statements很像存储过程,是一种运行在后台的SQL语句集合,我们可以从使用 prepared statements 获得很多好处,无论是性能问题还是安全问题。

Prepared Statements 可以检查一些你绑定好的变量,这样可以保护你的程序不会受到“SQL注入式”攻击。当然,你也可以手动地检查你的这些变量,然而,手动的检查容易出问题,而且很经常会被程序员忘了。当我们使用一些framework或是ORM的时候,这样的问题会好一些。

在性能方面,当一个相同的查询被使用多次的时候,这会为你带来可观的性能优势。你可以给这些Prepared Statements定义一些参数,而MySQL只会解析一次。

虽然最新版本的MySQL在传输Prepared Statements是使用二进制形势,所以这会使得网络传输非常有效率。

当然,也有一些情况下,我们需要避免使用Prepared Statements,因为其不支持查询缓存。但据说版本5.1后支持了。

在PHP中要使用prepared statements,你可以查看其使用手册:mysqli 扩展 或是使用数据库抽象层,如: PDO.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
// 创建 prepared statement
if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) {
    // 绑定参数
    $stmt->bind_param("s", $state);
    // 执行
    $stmt->execute();
    // 绑定结果
    $stmt->bind_result($username);
    // 移动游标
    $stmt->fetch();
    printf("%s is from %s\n", $username, $state);
    $stmt->close();
}

13. 无缓冲的查询

正常的情况下,当你在当你在你的脚本中执行一个SQL语句的时候,你的程序会停在那里直到没这个SQL语句返回,然后你的程序再往下继续执行。你可以使用无缓冲查询来改变这个行为。

关于这个事情,在PHP的文档中有一个非常不错的说明: mysql_unbuffered_query() 函数:

“mysql_unbuffered_query() sends the SQL query query to MySQL without automatically fetching and buffering the result rows as mysql_query() does. This saves a considerable amount of memory with SQL queries that produce large result sets, and you can start working on the result set immediately after the first row has been retrieved as you don’t have to wait until the complete SQL query has been performed.”

上面那句话翻译过来是说,mysql_unbuffered_query() 发送一个SQL语句到MySQL而并不像mysql_query()一样去自动fethch和缓存结果。这会相当节约很多可观的内存,尤其是那些会产生大量结果的查询语句,并且,你不需要等到所有的结果都返回,只需要第一行数据返回的时候,你就可以开始马上开始工作于查询结果了。

然而,这会有一些限制。因为你要么把所有行都读走,或是你要在进行下一次的查询前调用 mysql_free_result() 清除结果。而且, mysql_num_rows() 或 mysql_data_seek() 将无法使用。所以,是否使用无缓冲的查询你需要仔细考虑。

14. 把IP地址存成 UNSIGNED INT

很多程序员都会创建一个 VARCHAR(15) 字段来存放字符串形式的IP而不是整形的IP。如果你用整形来存放,只需要4个字节,并且你可以有定长的字段。而且,这会为你带来查询上的优势,尤其是当你需要使用这样的WHERE条件:IP between ip1 and ip2。

我们必需要使用UNSIGNED INT,因为 IP地址会使用整个32位的无符号整形。

而你的查询,你可以使用 INET_ATON() 来把一个字符串IP转成一个整形,并使用 INET_NTOA() 把一个整形转成一个字符串IP。在PHP中,也有这样的函数 ip2long() 和 long2ip()

1
$r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";

15. 固定长度的表会更快

如果表中的所有字段都是“固定长度”的,整个表会被认为是 “static” 或 “fixed-length”。 例如,表中没有如下类型的字段: VARCHAR,TEXT,BLOB。只要你包括了其中一个这些字段,那么这个表就不是“固定长度静态表”了,这样,MySQL 引擎会用另一种方法来处理。

固定长度的表会提高性能,因为MySQL搜寻得会更快一些,因为这些固定的长度是很容易计算下一个数据的偏移量的,所以读取的自然也会很快。而如果字段不是定长的,那么,每一次要找下一条的话,需要程序找到主键。

并且,固定长度的表也更容易被缓存和重建。不过,唯一的副作用是,固定长度的字段会浪费一些空间,因为定长的字段无论你用不用,他都是要分配那么多的空间。

使用“垂直分割”技术(见下一条),你可以分割你的表成为两个一个是定长的,一个则是不定长的。

16. 垂直分割

“垂直分割”是一种把数据库中的表按列变成几张表的方法,这样可以降低表的复杂度和字段的数目,从而达到优化的目的。(以前,在银行做过项目,见过一张表有100多个字段,很恐怖)

示例一:在Users表中有一个字段是家庭地址,这个字段是可选字段,相比起,而且你在数据库操作的时候除了个人信息外,你并不需要经常读取或是改写这个字段。那么,为什么不把他放到另外一张表中呢? 这样会让你的表有更好的性能,大家想想是不是,大量的时候,我对于用户表来说,只有用户ID,用户名,口令,用户角色等会被经常使用。小一点的表总是会有好的性能。

示例二: 你有一个叫 “last_login” 的字段,它会在每次用户登录时被更新。但是,每次更新时会导致该表的查询缓存被清空。所以,你可以把这个字段放到另一个表中,这样就不会影响你对用户ID,用户名,用户角色的不停地读取了,因为查询缓存会帮你增加很多性能。

另外,你需要注意的是,这些被分出去的字段所形成的表,你不会经常性地去Join他们,不然的话,这样的性能会比不分割时还要差,而且,会是极数级的下降。

17. 拆分大的 DELETE 或 INSERT 语句

如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询,你需要非常小心,要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的,表一锁住了,别的操作都进不来了。

Apache 会有很多的子进程或线程。所以,其工作起来相当有效率,而我们的服务器也不希望有太多的子进程,线程和数据库链接,这是极大的占服务器资源的事情,尤其是内存。

如果你把你的表锁上一段时间,比如30秒钟,那么对于一个有很高访问量的站点来说,这30秒所积累的访问进程/线程,数据库链接,打开的文件数,可能不仅仅会让你泊WEB服务Crash,还可能会让你的整台服务器马上掛了。

所以,如果你有一个大的处理,你定你一定把其拆分,使用 LIMIT 条件是一个好的方法。下面是一个示例:

1
2
3
4
5
6
7
8
9
10
while (1) {
    //每次只做1000条
    mysql_query("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000");
    if (mysql_affected_rows() == 0) {
        // 没得可删了,退出!
        break;
    }
    // 每次都要休息一会儿
    usleep(50000);
}

18. 越小的列会越快

对于大多数的数据库引擎来说,硬盘操作可能是最重大的瓶颈。所以,把你的数据变得紧凑会对这种情况非常有帮助,因为这减少了对硬盘的访问。

参看 MySQL 的文档 Storage Requirements 查看所有的数据类型。

如果一个表只会有几列罢了(比如说字典表,配置表),那么,我们就没有理由使用 INT 来做主键,使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。如果你不需要记录时间,使用 DATE 要比 DATETIME 好得多。

当然,你也需要留够足够的扩展空间,不然,你日后来干这个事,你会死的很难看,参看Slashdot的例子(2009年11月06日),一个简单的ALTER TABLE语句花了3个多小时,因为里面有一千六百万条数据。

19. 选择正确的存储引擎

在 MySQL 中有两个存储引擎 MyISAM 和 InnoDB,每个引擎都有利有弊。酷壳以前文章《MySQL: InnoDB 还是 MyISAM?》讨论和这个事情。

MyISAM 适合于一些需要大量查询的应用,但其对于有大量写操作并不是很好。甚至你只是需要update一个字段,整个表都会被锁起来,而别的进程,就算是读进程都无法操作直到读操作完成。另外,MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。

InnoDB 的趋势会是一个非常复杂的存储引擎,对于一些小的应用,它会比 MyISAM 还慢。他是它支持“行锁” ,于是在写操作比较多的时候,会更优秀。并且,他还支持更多的高级应用,比如:事务。

下面是MySQL的手册

20. 使用一个对象关系映射器(Object Relational Mapper)

使用 ORM (Object Relational Mapper),你能够获得可靠的性能增涨。一个ORM可以做的所有事情,也能被手动的编写出来。但是,这需要一个高级专家。

ORM 的最重要的是“Lazy Loading”,也就是说,只有在需要的去取值的时候才会去真正的去做。但你也需要小心这种机制的副作用,因为这很有可能会因为要去创建很多很多小的查询反而会降低性能。

ORM 还可以把你的SQL语句打包成一个事务,这会比单独执行他们快得多得多。

目前,个人最喜欢的PHP的ORM是:Doctrine

21. 小心“永久链接”

“永久链接”的目的是用来减少重新创建MySQL链接的次数。当一个链接被创建了,它会永远处在连接的状态,就算是数据库操作已经结束了。而且,自从我们的Apache开始重用它的子进程后——也就是说,下一次的HTTP请求会重用Apache的子进程,并重用相同的 MySQL 链接。

在理论上来说,这听起来非常的不错。但是从个人经验(也是大多数人的)上来说,这个功能制造出来的麻烦事更多。因为,你只有有限的链接数,内存问题,文件句柄数,等等。

而且,Apache 运行在极端并行的环境中,会创建很多很多的了进程。这就是为什么这种“永久链接”的机制工作地不好的原因。在你决定要使用“永久链接”之前,你需要好好地考虑一下你的整个系统的架构。

MySql查询不区分大小写解决方案(两种)

在mysql中查询数据能不区分大小写吗,下面小编给大家通过两种方案解决MySql查询不区分大小写,有需要的朋友可以借鉴下

当我们输入不管大小写都能查询到数据,例如:输入 aaa 或者aaA ,AAA都能查询同样的结果,说明查询条件对大小写不敏感。

解决方案一:

于是怀疑Mysql的问题。做个实验:直接使用客户端用sql查询数据库。 发现的确是大小不敏感 。

通过查询资料发现需要设置collate(校对) 。 collate规则

*_bin: 表示的是binary case sensitive collation,也就是说是区分大小写的
*_cs: case sensitive collation,区分大小写
*_ci: case insensitive collation,不区分大小写

解决方法。

1.可以将查询条件用binary()括起来。  比如:

select * from TableA where binary columnA ='aaa';

2. 可以修改该字段的collation 为 binary

比如:

ALTER TABLE TABLENAME MODIFY COLUMN COLUMNNAME VARCHAR(50) BINARY CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL;

解决方案二:

mysql查询默认是不区分大小写的 如:

select * from some_table where str=‘abc';
select * from some_table where str='ABC';

得到的结果是一样的,如果我们需要进行区分的话可以按照如下方法来做:

第一种方法:
要让mysql查询区分大小写,可以:

select * from some_table where binary str='abc'
select * from some_table where binary str='ABC'

第二方法:

在建表时时候加以标识

create table some_table(
   str char(20) binary 
)

原理:

对于CHAR、VARCHAR和TEXT类型,BINARY属性可以为列分配该列字符集的 校对规则。BINARY属性是指定列字符集的二元 校对规则的简写。排序和比较基于数值字符值。因此也就自然区分了大小写。

 

一个mysql多列索引的问题

这周工作时曾遇到一个问题。在一个MYSQL的表里做类似下面这一个很简单查询的时候耗时接近1秒钟的时间。

select sum(col5) , sum(col6) from table_name
where col_key_2='value1' and col_key_3 = 'value2'

表定义如下:

CREATE TABLE `table_name` (
  `col_key_1` date NOT NULL default '0000-00-00',
  `col_key_3` varchar(32) NOT NULL default '',
  `col_key_2` varchar(32) NOT NULL default '',
  `col5` bigint(20) unsigned default NULL,
  `col6` bigint(20) unsigned default NULL,
  `col7` bigint(20) unsigned default NULL,
  `col8` bigint(20) unsigned default NULL,
  `col_key_4` varchar(32) NOT NULL default '',
  PRIMARY KEY  (`col_key_1`,`col_key_2`,`col_key_3`,`col_key_4`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

整个表里大概只有200多万条数据。但查询的速度居然会慢到1秒钟才能查询出来,完全不可以忍受。

然后我给这张加上了另一个索引:KEY `class` (`col_key_2`,`col_key_3`)

查询的速度立马提高到0.00秒。

于是认真的查看了一下mysql 手册的8.3小节。

MySQL索引的种类和作用

mysql的索引分成:primary key, unique, index, fulltext index。 primary key是主键, unique是唯一索引, index是普通的索引。fulltext index是全文索引。 索引的作用就像C语言里的指针那样,直接指向表的一行。

可以对用col_name(N) 对符串的前N个字节做索引。 text类型和blob类型则必须要对前N个字节做索引。MYISAM最多支持1000个字节的索引, INNODB最多支持767字节的索引。

索引有下列作用:

1 帮助where语句快速查询。

2 进行多表连接

3 找到最大值和最小值(应该只有B-tree索引有这个功能,hash索引没有这个功能)

4 sort(应该只有B-tree索引有这个功能,hash索引没有这个功能)和group

多列索引

多列索引在对多个列同时进行查询的时候特别有用。多列索引最多支持16列。可以这样理解多列索引:

把多个列concat在一起,然后再对这个concat的值做一个索引。

比较神奇的一点是,比如你有一个索引针对col1 col2 col3这3个列时, 只查询col1和只查询col1 col2时也能用到这个索引。

比如有这个表:

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

下面这些查询都可以用到多列索引:

SELECT * FROM test WHERE last_name='Widenius';

SELECT * FROM test
  WHERE last_name='Widenius' AND first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius'
  AND (first_name='Michael' OR first_name='Monty');

SELECT * FROM test
  WHERE last_name='Widenius'
  AND first_name >='M' AND first_name < 'N';

下面这些查询不能用到多列索引:

SELECT * FROM test WHERE first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius' OR first_name='Michael';

你可以在sql语句前使用explain语句来确定是否用到了索引。

比如下面这个查询就可以用到class这个索引

mysql> explain select sum(col5) , sum(col6) from table_name 
where col_key_2='value1' and col_key_3 = 'value2' \G 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: table_name
         type: ref
possible_keys: class
          key: class
      key_len: 68
          ref: const,const
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

而下面这个查询则不能使用到索引:

mysql> explain select sum(col5) , sum(col6) from table_name
 where col5='value1' and col_key_3 = 'value2' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: table_name
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2357455
        Extra: Using where
1 row in set (0.00 sec)

索引的好坏

MySQL使用一个指标value group size来衡量索引的好坏。什么是value group呢? 就是具有相同索引key值的行数。这个指标显然是越小越好。最理想的情况就是每一个key值只对应1行, 这样的话我们的每次搜索一个key值都只返回一行,显然速度非常快。

可以用mysql提供的工具查看一个表的索引的好坏。可以先用analyze table语句更新统计,然后用show index来查看统计:

mysql> analyze table table_name;
+-----------------+---------+----------+----------+
| Table           | Op      | Msg_type | Msg_text |
+-----------------+---------+----------+----------+
| stat.table_name | analyze | status   | OK       |
+-----------------+---------+----------+----------+
1 row in set (3.13 sec)

mysql> show index in table_name;

table_name这张表有两个索引PRIMARY和class,PRIMARY这个索引是一个包含4列的多列索引。

Cardinality这个值表示索引值的不同的行数。

例如:

col_key_1值有18行。

col_key_1+col_key_2 值有392909行。

col_key_1 + col_key_2 + col_key_3 值有235745行。

col_key_1 + col_key_2 + col_key_3 + col_key_4值有235745行。

通过索引值的行数,我们就可以看出来索引好还是不好了。索引值不同的行数越多索引就越好。当索引值不同的行数=表的总行数就达到最理想的情况 value group size = 1了。

B-tree索引和Hash索引的比较

默认情况下MySQL都是使用B-tree索引。来谈一下Hash索引的缺陷:

1 只能处理’=‘ 这种where 子句,而对于< >是无能为力的。 这和B-tree索引是有序的,Hash无序的有关。

2 无法处理order by。 原因同上。

3 无法得知两行之间的距离。 原因同上。

4 只能搜完整的字段,不能只搜字段的一部分。 而对于B-tree索引, 支持搜索字符串最左边的一部分。例如”police%” 。

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即可。