mysql 关联查询 索引失效

接手一个新项目数据导出需求 大致sql如下 select b.* from wms_sku_uuids a inner JOIN wms_entity_operate_log b on a.uuid=b.entity_code where a.uuid="00617531" and a.`status`=9 附表大约650w条记录 explain 发现附表每次都是全量查找 怀疑是不是sql语句的问题 于是换成where in 子查询的方式附表还是全量 show create table   查询都覆盖到的索引 百度后发现数据类型不同或相同类型字符编码不同会造成不走索引于是 SHOW FULL COLUMNS FROM wms_sku_uuids; 1556009056879 发现字符编码的确不同  字符编码同步后 1556009283207  

mysql 批量建表 存储过程

delimiter // CREATE procedure create_table() BEGIN DECLARE `@i` int(11); DECLARE `@sqlstr` varchar(65000); SET `@i`=0; WHILE `@i` < 256 DO SET @sqlstr = CONCAT( "CREATE TABLE box_item_pool_", `@i`, "( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `_unique` varchar(64) NOT NULL DEFAULT '' COMMENT '分表后唯一标示', `user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户ID', `box_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '盒子ID', `prod_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商品ID, 目前是product_master.id', `prod_code` varchar(32) NOT NULL DEFAULT '' COMMENT '商品CODE, 目前是product_master.code', `product_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'product.id', `created_at` datetime DEFAULT NULL, `updated_at` datetime DEFAULT NULL, `deleted_at` datetime DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `_unique` (`_unique`) USING BTREE, KEY `user_box` (`user_id`,`box_id`), KEY `box` (`box_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;" ); prepare stmt from @sqlstr; execute stmt; SET `@i` = `@i` + 1; END WHILE; END; call create_table(); drop procedure create_table; //

tshark 抓取mysql查询语句 及 返回结果

一直使用tshark抓取sql语句调试代码  这条命令只能显示查询语句 故对此命令做了修改 使其能显示查询结果 tshark -s 512 -i eth0 -n -f  'tcp dst port 3306' -R 'mysql.query' -T fields -e mysql.query 修改后 sudo tshark -i lo -n -f 'tcp port 3306' -V | awk -v y=0 -v i=0 '{if($0~/State/){text_in=index($0,":");print substr($0,text_in+1)}if($0~'/Name/'){text_in=index($0,":");row[i]=substr($0,text_in+1);i++}if($0~/text/){if(y<=i){yy=y;y++}if(y>=i){y=0}text_in=index($0,":");print row[yy] "->" substr($0,text_in+1)}if($0~'/Frame/'){i=0;y=0;delete row}} END{y=0;while(y<=i){y++}}' 开启对应的端口后 tshark

要在mac os上安装 mysqldb

首先,下载MySQLdb:http://sourceforge.net/projects/mysql-python/

下载MySQL-python-1.2.3.tar.gz

解压,运行setup.py:
python setup.py install
报错:
sh: mysql_config: command not found Traceback (most recent call last): File “setup.py”, line 15, in <module> metadata, options = get_config() File “/Users/***/Downloads/MySQL-python-1.2.3/setup_posix.py”, line 43, in get_config libs = mysql_config(“libs_r”) File “/Users/***/Downloads/MySQL-python-1.2.3/setup_posix.py”, line 24, in mysql_config raise EnvironmentError(“%s not found” % (mysql_config.path,)) EnvironmentError: mysql_config not found
参考:http://www.southsearepublic.org/article/2416/read/environmenterror_mysql_config_not_found/installing_mysqldb_for_python_on_mac_osx/
发现要修改site.cfg
mysql_config = /usr/local/mysql/bin/mysql_config
让mysql_config指向mysql在mac os中的安装位置。
之后再用高权限运行
sudo python setup.py install
如果运行的时候,还有问题:
>>> import MySQLdb /Library/Python/2.7/site-packages/MySQL_python-1.2.3-py2.7-macosx-10.7-intel.egg/_mysql.py:3: UserWarning: Module _mysql was already imported from /Library/Python/2.7/site-packages/MySQL_python-1.2.3-py2.7-macosx-10.7-intel.egg/_mysql.pyc, but /Users/***/Downloads/MySQL-python-1.2.3 is being added to sys.path Traceback (most recent call last): File “<stdin>”, line 1, in <module> File “MySQLdb/__init__.py”, line 19, in <module> import _mysql File “build/bdist.macosx-10.7-intel/egg/_mysql.py”, line 7, in <module> File “build/bdist.macosx-10.7-intel/egg/_mysql.py”, line 6, in __bootstrap__ ImportError: dlopen(/Users/***/.python-eggs/MySQL_python-1.2.3-py2.7-macosx-10.7-intel.egg-tmp/_mysql.so, 2): Library not loaded: libmysqlclient.18.dylib Referenced from: /Users/***/.python-eggs/MySQL_python-1.2.3-py2.7-macosx-10.7-intel.egg-tmp/_mysql.so Reason: image not found
那么就需要解决动态引入的问题了(重新做一遍,并且设置环境变量):
$ sudo python setup.py clean $ sudo python setup.py build $ sudo python setup.py install $ export DYLD_LIBRARY_PATH=/usr/local/mysql/lib:$DYLD_LIBRARY_PATH $python >>> import MySQLdb /Library/Python/2.7/site-packages/MySQL_python-1.2.3-py2.7-macosx-10.7-intel.egg/_mysql.py:3: UserWarning: Module _mysql was already imported from /Library/Python/2.7/site-packages/MySQL_python-1.2.3-py2.7-macosx-10.7-intel.egg/_mysql.pyc, but /Users/***/Downloads/MySQL-python-1.2.3 is being added to sys.path >>> conn=MySQLdb.connect(host=”localhost”,user=”root”,passwd=”root”,db=”mysql”) >>> cursor =conn.cursor() >>> sql =”select * from user” >>> cursor.execute(sql) 7L >>> row=cursor.fetchone() >>> print row (‘localhost’, ‘root’, ‘*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ”, ”, ”, ”, 0L, 0L, 0L, 0L, ”, ”) 能够读取mysql.user表的信息了,说明已经mysqldb已经安装成功。 参考:http://stackoverflow.com/questions/1465846/install-mysqldb-on-snow-leopard/6537345#6537345

PHP连接MySQL报错"No such file or directory"的解决办法

1,首先确定是mysql_connect()和mysql_pconnect()的问题,故障现象就是函数返回空,而mysql_error()返回“No such file or directory” 2,写个phpinfo页面,找到mysql.default_socket、mysqli.default_socket、pdo_mysql.default_socket 3,启动mysql,执行命令 STATUS; 记下UNIX socket的值 4,打开php.ini(可以从phpinfo页面中找到php.ini的位置,默认是/private/etc/php.ini),将三个配置项的值改成mysql.sock的值。 分别为:mysql.default_socket、mysqli.default_socket、pdo_mysql.default_socket) 5,重启apache

mysql分组取每组前几条记录(排序)

  -- ---------------------------- -- Table structure for mygoods -- ---------------------------- DROP TABLE IF EXISTS `mygoods`; CREATE TABLE `mygoods` ( `goods_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `cat_id` int(11) NOT NULL DEFAULT '0', `price` tinyint(3) NOT NULL DEFAULT '0', `status` tinyint(3) DEFAULT '1', PRIMARY KEY (`goods_id`), KEY `icatid` (`cat_id`) ) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of mygoods -- ---------------------------- INSERT INTO `mygoods` VALUES ('1', '101', '90', '0'); INSERT INTO `mygoods` VALUES ('2', '101', '99', '1'); INSERT INTO `mygoods` VALUES ('3', '102', '98', '0'); INSERT INTO `mygoods` VALUES ('4', '103', '96', '0'); INSERT INTO `mygoods` VALUES ('5', '102', '95', '0'); INSERT INTO `mygoods` VALUES ('6', '102', '94', '1'); INSERT INTO `mygoods` VALUES ('7', '102', '93', '1'); INSERT INTO `mygoods` VALUES ('8', '103', '99', '1'); INSERT INTO `mygoods` VALUES ('9', '103', '98', '1'); INSERT INTO `mygoods` VALUES ('10', '103', '97', '1'); INSERT INTO `mygoods` VALUES ('11', '104', '96', '1'); INSERT INTO `mygoods` VALUES ('12', '104', '95', '1'); INSERT INTO `mygoods` VALUES ('13', '104', '94', '1'); INSERT INTO `mygoods` VALUES ('15', '101', '92', '1'); INSERT INTO `mygoods` VALUES ('16', '101', '93', '1'); INSERT INTO `mygoods` VALUES ('17', '101', '94', '0'); INSERT INTO `mygoods` VALUES ('18', '102', '99', '1'); INSERT INTO `mygoods` VALUES ('19', '105', '85', '1'); INSERT INTO `mygoods` VALUES ('20', '105', '89', '0'); INSERT INTO `mygoods` VALUES ('21', '105', '99', '1');
说明: 表mygoods为商品表,cat_id为分类id,goods_id为商品id,status为商品当前的状态位(1:有效,0:无效)。 需求:每个分类下,找出两个价格最高的有效的商品。 1.每个分类找出价格最高的两个商品
mysql> select a.*   
    -> from mygoods a   
    -> where (select count(*) 
    -> from mygoods 
    -> where cat_id = a.cat_id and price > a.price  ) <2 
    -> order by a.cat_id,a.price desc;
+----------+--------+-------+--------+
| goods_id | cat_id | price | status |
+----------+--------+-------+--------+
|        2 |    101 |    99 |      1 |
|       17 |    101 |    94 |      0 |
|       18 |    102 |    99 |      1 |
|        3 |    102 |    98 |      0 |
|        8 |    103 |    99 |      1 |
|        9 |    103 |    98 |      1 |
|       11 |    104 |    96 |      1 |
|       12 |    104 |    95 |      1 |
|       21 |    105 |    99 |      1 |
|       20 |    105 |    89 |      0 |
+----------+--------+-------+--------+
10 rows in set (0.00 sec)
2.每个分类找出价格最高的有效的两个商品(正确)
mysql> select a.* 
    -> from mygoods a 
    -> where (select count(*) from mygoods 
    -> where cat_id = a.cat_id and price > a.price and status=1  ) <2 
    -> and status=1 
    -> order by a.cat_id,a.price desc ;
+----------+--------+-------+--------+
| goods_id | cat_id | price | status |
+----------+--------+-------+--------+
|        2 |    101 |    99 |      1 |
|       16 |    101 |    93 |      1 |
|       18 |    102 |    99 |      1 |
|        6 |    102 |    94 |      1 |
|        8 |    103 |    99 |      1 |
|        9 |    103 |    98 |      1 |
|       11 |    104 |    96 |      1 |
|       12 |    104 |    95 |      1 |
|       21 |    105 |    99 |      1 |
|       19 |    105 |    85 |      1 |
+----------+--------+-------+--------+
10 rows in set (0.00 sec)
3.每个分类找出价格最高的有效的两个商品(正确)
mysql> select a.* 
    -> from mygoods a 
    -> left join mygoods b 
    -> on a.cat_id = b.cat_id and a.price < b.price and b.status=1
    -> where a.status=1
    -> group by a.goods_id,a.cat_id,a.price
    -> having count(b.goods_id) < 2
    -> order by a.cat_id,a.price desc;
+----------+--------+-------+--------+
| goods_id | cat_id | price | status |
+----------+--------+-------+--------+
|        2 |    101 |    99 |      1 | 
|       16 |    101 |    93 |      1 | 
|       18 |    102 |    99 |      1 | 
|        6 |    102 |    94 |      1 | 
|        8 |    103 |    99 |      1 | 
|        9 |    103 |    98 |      1 | 
|       11 |    104 |    96 |      1 | 
|       12 |    104 |    95 |      1 | 
|       21 |    105 |    99 |      1 | 
|       19 |    105 |    85 |      1 | 
+----------+--------+-------+--------+
10 rows in set (0.00 sec)
4.每个分类找出价格最高的有效的两个商品(错误)
mysql> select a.* 
    -> from mygoods a 
    -> where (select count(*) from mygoods 
    -> where cat_id = a.cat_id and price > a.price  ) <2 and status=1 
    -> order by a.cat_id,a.price desc;
+----------+--------+-------+--------+
| goods_id | cat_id | price | status |
+----------+--------+-------+--------+
|        2 |    101 |    99 |      1 |
|       18 |    102 |    99 |      1 |
|        8 |    103 |    99 |      1 |
|        9 |    103 |    98 |      1 |
|       11 |    104 |    96 |      1 |
|       12 |    104 |    95 |      1 |
|       21 |    105 |    99 |      1 |
+----------+--------+-------+--------+
7 rows in set (0.00 sec)
由上可知,如果需要增加条件的话,需要在两处增加条件。
可以将每个分组下的goods_id合并。
mysql> select cat_id,GROUP_CONCAT(goods_id) from mygoods group by cat_id;
+--------+------------------------+
| cat_id | GROUP_CONCAT(goods_id) |
+--------+------------------------+
|    101 | 1,2,15,16,17           |
|    102 | 3,5,6,7,18             |
|    103 | 4,8,9,10               |
|    104 | 11,12,13               |
|    105 | 19,20,21               |
+--------+------------------------+
5 rows in set (0.00 sec)
转自 http://blog.sina.com.cn/s/blog_4c197d420101e408.html

MySQL concat函数的使用

MySQL concat函数是MySQL数据库中众多的函数之一,下文将对MySQL concat函数的语法和使用进行说明,供您参考和学习。 MySQL concat函数使用方法: CONCAT(str1,str2,…) 返回结果为连接参数产生的字符串。如有任何一个参数为NULL ,则返回值为 NULL。 注意: 如果所有参数均为非二进制字符串,则结果为非二进制字符串。 如果自变量中含有任一二进制字符串,则结果为一个二进制字符串。 一个数字参数被转化为与之相等的二进制字符串格式;若要避免这种情况,可使用显式类型 cast, 例如: SELECT CONCAT(CAST(int_col AS CHAR), char_col) MySQL concat函数可以连接一个或者多个字符串,如
  1. mysql> select concat('10');
  2. +--------------+
  3. | concat('10') |
  4. +--------------+
  5. | 10   |
  6. +--------------+
  7. 1 row in set (0.00 sec)
  8. mysql> select concat('11','22','33');
  9. +------------------------+
  10. | concat('11','22','33') |
  11. +------------------------+
  12. | 112233 |
  13. +------------------------+
  14. 1 row in set (0.00 sec)
MySQL的concat函数在连接字符串的时候,只要其中一个是NULL,那么将返回NULL
  1. mysql> select concat('11','22',null);
  2. +------------------------+
  3. | concat('11','22',null) |
  4. +------------------------+
  5. | NULL   |
  6. +------------------------+
  7. 1 row in set (0.00 sec)