Archive for category MySQL

mysql数据库中空名字的用户

Posted by Neo in MySQL on 2011/11/15

mysql中的mysql.user表里面千万不能有名字为空或者null的用户，修改起来伤不起啊

如何–skip-grant-tables都没用，当发现有了空的用户名，就会茅厕顿开

没有评论

thinking_sphinx ambiguous error

Posted by Neo in MySQL on 2011/09/03

ERROR: index ‘listing_core’: sql_range_query: Column ‘latitude’ in
field list is ambiguous (DSN=mysql://root:***@localhost:3306/
listings_development).

这个就是mysql多表查询时，相同字段的冲突问题！

原来用thinking_sphinx在生成索有链接表得时候，那么有得时候会这样处理

class A
  has_many :bs
  define_index do
    indexes "LOWER(name)", :as => name
    indexes  bs.name, :as => bs_name
  end

as表中有name列，bs表中也有，那么sphinx在生成索引得时候就不知道用哪个了，所以它觉得“暧昧”!

修正方法

indexes "LOWER(as.name)", :as => :name

没有评论

mysql存储过程学习-分割字符串

Posted by Neo in MySQL on 2011/04/25

DELIMITER //

DROP PROCEDURE IF EXISTS split_string;
CREATE PROCEDURE split_string(IN to_split VARCHAR(255), IN split_with VARCHAR(100))
BEGIN
  DECLARE total_length INT;
  DECLARE location INT;
  DROP TEMPORARY TABLE IF EXISTS temp_store;
  CREATE TEMPORARY TABLE temp_store(str VARCHAR(100));

  WHILE LENGTH(to_split) > 0  DO
    SET total_length = LENGTH(to_split);
    SET location     = LOCATE(split_with, to_split);
    IF location = 0 THEN
      INSERT INTO temp_store(str) VALUE(to_split);
      SET to_split = '';
    ELSE   
      INSERT INTO temp_store(str) VALUE(LEFT(to_split, location-1));
      SET to_split = RIGHT(to_split, total_length-location);
    END IF;  
  END WHILE;

  SELECT * FROM temp_store;
END
//
DELIMITER ;

没有评论

当心where中的常量

Posted by Neo in MySQL on 2011/04/16

mysql> SELECT COUNT(*) FROM articles_categories WHERE id=1;
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 ROW IN SET (0.00 sec)

mysql> SELECT COUNT(*) FROM articles_categories WHERE id=1 OR 1<=1;
+----------+
| COUNT(*) |
+----------+
|      136 |
+----------+
1 ROW IN SET (0.00 sec)

到底发生了什么？

mysql> EXPLAIN extended SELECT COUNT(*) FROM articles_categories WHERE id=1 OR 1<=1;
mysql> SHOW warnings\G

*************************** 1. ROW ***************************
  Level: Note
   Code: 1003
Message: SELECT COUNT(0) AS `count(*)` FROM `demo`.`articles_categories` WHERE 1
1 ROW IN SET (0.00 sec)

是了where id=1 or 1<=1和where 1是等同的

没有评论

mysql loose index scan

Posted by Neo in MySQL on 2011/03/07

场景：group by、distinct
表users:

CREATE TABLE people(
    user_id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    province VARCHAR(100) NOT NULL,
    city VARCHAR(100) NOT NULL,
    street VARCHAR(150) NOT NULL,
    PRIMARY KEY(id),
    INDEX pos(province,city,street)
);

这里向people随机插入60000条数据

看下面的查询:

 EXPLAIN  SELECT province,city,street
             FROM people WHERE province='Beijing' AND city='Haidian'
             GROUP BY province,city,street;

在Extra中会看到这样的现实 Using where; Using index for group-by

这样说明mysql在查询中使用了loose index scan(松散的索引扫描)

原理：
1.mysql首先扫描索引，找到key中的province是Beijing的
2.继续扫描索引，查询到city是Haidian的，这样就组成了一个元组,中间跳过了类似这样的索引
3.继续扫描剩下的以开头的索引，扫描完成后通过索引的指针得到查询结果，在这个时候，mysql已经不需要再去执行group by了，因为得到的结果已经是分完组的

最坏的时候是不存在类似province=’Beijing’或者province>’Beijing’这样的情况，那么，查询将扫描全表的索引，然后分组
从上面可以看出，所给出的条件越多，需要扫描的索引就越少
支持max,min的查询，因为max就是取最后一行，而min就是取第一行

loose index scan的适用情况

扫描全部的索引

SELECT province,city FROM people GROUP BY province,city;
        SELECT DISTINCT province,city FROM people GROUP BY province,city
    SELECT province,city FROM people
            WHERE province='Beijing' GROUP BY province,city;

这个是取第一个结果就OK了

SELECT province,MIN(city) FROM people GROUP BY province
    SELECT street,city,province FROM people GROUP BY province,city
    SELECT province,city FROM people
             WHERE street='111' GROUP BY province,city

没有评论