订阅本站

实例分析mysql left join,right join,inner join的区别

huangdijia 发表于 2011-9-18 分类 MySQL数据库技术 | 发表评论

下面是例子分析
表A记录如下：
aID aNum
1 a20050111
2 a20050112
3 a20050113
4 a20050114
5 a20050115

表B记录如下:
bID bName
1 2006032401
2 2006032402
3 2006032403
4 2006032404
8 2006032408

创建这两个表SQL语句如下：
CREATE TABLE a
aID int( 1 ) AUTO_INCREMENT PRIMARY KEY ,
aNum char( 20 )
)
CREATE TABLE b(
bID int( 1 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
bName char( 20 )
)
查看全文

mysqlhotcopy错误Can’t locate DBI.pm解决办法

huangdijia 发表于 2011-9-8 分类 MySQL数据库技术, Web服务器 | 发表评论

Can't locate DBI.pm in @INC (@INC contains: /etc/perl /usr/local/lib/perl/5.10.1 /usr/local/share/perl/5.10.1 /usr/lib/perl5 /usr/share/perl5 /usr/lib/perl/5.10 /usr/share/perl/5.10 /usr/local/lib/site_perl .) at /usr/local/webserver/mysql/bin/mysqlhotcopy line 8.
BEGIN failed--compilation aborted at /usr/local/mysql/bin/mysqlhotcopy line 8.
出现上述错误的原因是mysqlhotcopy本身是一个perl脚本，而perl缺少了一个DB_File的模块所产生的。
解决办法：
apt-get install libclass-dbi-mysql-perl
此方法适用于debian,ubuntu，centos可以改用yum试试。

MySQL查询按IN顺序输出结果

huangdijia 发表于 2011-8-12 分类 MySQL数据库技术 | 发表评论

如果使用in查询的话，输出的结果是重新排序的，而不按输入的id顺序输出的

1、可以使用FIND_IN_SET建立一个排序列

2、自己构建一个顺序id表，左连接IN查询结果集

3、使用UNION

MySQL遇到的两个问题

huangdijia 发表于 2011-7-27 分类 MySQL数据库技术 | 发表评论

COUNT(DISTINCT `field`)是魔鬼，数据量稍大一些，执行此操作就会提示内存不足。
建议改用临时表加GROUP BY的方法来解决。

SELECT COUNT(*) FROM (SELECT `field` FROM `table` GROUP BY `field`) a LIMIT 1;

插入数据时MYSQL返回#1062 – Duplicate entry ’127′ for key ‘PRIMARY’，这种问题是没有规律的，不一定什么时候就出现了，不过一般是数据量比较大的时候，比如达到6位数出现的几率就多了一点。

如何正确的重置MySQL密码

huangdijia 发表于 2011-6-12 分类 MySQL数据库技术 | 发表评论

关键点是：在使用skip-grant-tables参数的同时，还要加上skip-networking参数：

shell> mysqld_safe --skip-grant-tables --skip-networking &

接着使用SQL重置密码后，记得去掉skip-networking，以正常方式重启MySQL服务：

shell> /etc/init.d/mysqld restart

上面的方法需要重启两次服务，实际上还能更优雅一点，重启一次即可：

首先需要把用到的SQL语句保存到一个文本文件里（/path/to/init/file）：

UPDATE `mysql`.`user` SET `Password`=PASSWORD('yourpassword') WHERE `User`='root' AND `Host`= '127.0.0.1';
FLUSH PRIVILEGES;

接着使用init-file参数启动MySQL服务，

shell> /etc/init.d/mysql stop
shell> mysqld_safe --init-file=/path/to/init/file &

此时，密码就已经重置了，最后别忘了删除文件内容，免得泄露密码。

提示：本文用到的参数都是通过命令行mysqld_safe传递的，实际上也可以通过my.cnf。

联想：是否可以通过init-file来初始化一个数据库或者导入数据呢？

参考：关于重置密码，官方文档里有专门的描述：How to Reset the Root Password。

mysql表删除重复记录方法总结及效率对比

huangdijia 发表于 2011-3-26 分类 MySQL数据库技术 | 发表评论

下面这些方法在我虚拟机上做的测试，内存384M,交换分区1024M, test共300W数据，重复记录3.5W，需求如题目所示，表结构如下：

CREATE DATABASE /*!32312 IF NOT EXISTS*/`test` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `test`;

/*Table structure for table `test` */

DROP TABLE IF EXISTS `test`;

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) DEFAULT NULL COMMENT '姓名',
  `age` tinyint(4) DEFAULT NULL COMMENT '年龄',
  `mate` tinyint(4) DEFAULT '1' COMMENT '有无配偶(1-有 0-无)',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_age` (`age`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

现有记录：

insert  into `test`(`id`,`name`,`age`,`mate`) values (2,'aaaaa',28,0),
(3,'bbbb',23,0),
(4,'cccc',25,1),
(5,'dddd',26,0),
(6,'eeee',24,0),
(7,'fffff',18,0),
(8,'eeee',40,1),
(9,'eeee',60,1);

想去掉name重名的记录，方法如下：
1.给name字段修改成唯一索引：

drop index idx_name on test;
alter table test add unique index (name);

这样当向表中添加相同记录的时候，会返回1062的添加失败信息。
但是有一种情况是表中已经有n个重复的记录，这时候我们才想起来要添加唯一索引，再执行上面的操作时，数据库会告诉你已经有重复的记录了，建立索引失败，这时候，我们可以用下面的操作：

alter ignore table test add unique idx_name (name);

它会删除重复的记录（别怕，会保留一条），然后建立唯一索引，高效而且人性化。 查看全文

MySql中如何用LIKE来查找带反斜线“\”的记录

huangdijia 发表于 2011-3-16 分类 MySQL数据库技术, PHP/PHP框架 | 1条评论

使用 \\\\ 还是 \\ 看你如何的定义字符串的。其实只有两处转义：PHP 中 和 MySQL 中。

PHP 中 如果你用双引号”" 定义字符串, 那么这里有一次转义。如果用单引号就不会转义。

$sql = “SELECT * FROM table WHERE col LIKE ‘%a\\\\%’ “;

这样实际上经过转义发给 MySQL 的是

SELECT * FROM table WHERE col LIKE ‘%a\\%’;

用单引号则不会转义（除了单引号,当然变量也不会展开）:

$sql = ‘SELECT * FROM table WHERE col LIKE \’%a\\%\’ ‘;

发给 MySQL 的也是

SELECT * FROM table WHERE col LIKE ‘%a\\%’;

最简便的备份MySql数据库方法

huangdijia 发表于 2011-3-13 分类 MySQL数据库技术 | 发表评论

使用MYSQL进行数据库备份，有很正规的数据库备份方法，同其他的数据库服务器有相同的概念，但有没有想过，MySQL会有更简捷的使用文件目录的备份方法，而且又快有好。

一、数据备份捷径

因为这个方法没有得到官方正式文档的验证，我们暂称为试验吧。

目的：备份hostA主机中一个mysql数据库TestA，并恢复到到hostB机中

试验环境：

操作系统：WinNT4.0，Mysql3.22.34，phpMyAdmin 2.1.0

在hostA中安装mysql数据库并建立TestA数据库

hostB机安装mysql数据库，没有TestA数据库

方法步骤：

启动phpMyAdmin察看HostA和HostB中的数据库列表，在HostB中没有TestA数据库

找到HostA中mysql的安装目录，并找到数据库目录data

在我的试验环境中，这个目录是

C:\mysqldata

找到对应数据库名称的子目录

C:\mysqldataTestA

粘贴拷贝到HostB的Data目录下，是HostA同HostB Mysql数据目录下的文件相同

刷新HostB的phpMyAdmin察看一下数据库列表，我们看到TestA已经出现，并且作查询修改等操作都正常，备份恢复恢复成功

试验结论：Mysql的数据库可以通过文件形式保存，备份，恢复只要将相应文件目录恢复即可，无需使用其它工具备份。 查看全文