涉及表:tmpcard
表结构:mysql> desc tmpcard;
+--------+--------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------------+------+-----+---------+----------------+
| cardid | int(11) | NO | PRI | NULL | auto_increment |
| cardno | char(64) | NO | UNI | 0 | |
| uid | int(4) unsigned zerofill | NO | | 0000 | |
+--------+--------------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
需求:用导出前1000条数据成为文本文件。
SQL语句:SELECT GROUP_CONCAT(cardno separator '\\r\\n') FROM tmpcard WHERE uid < 1 ORDER BY cardid ASC LIMIT 1000 ; 但是奇怪的事情是,每次导出来只有一点点,而且末尾的的最后的那一个只有一半数据。测试数据:--
-- 表的结构 `tmpcard`
-- DROP TABLE IF EXISTS `tmpcard`;
CREATE TABLE IF NOT EXISTS `tmpcard` (
`cardid` int(11) NOT NULL auto_increment,
`cardno` char(64) NOT NULL default '0',
`uid` int(4) unsigned zerofill NOT NULL default '0000',
PRIMARY KEY (`cardid`),
UNIQUE KEY `newcard` (`cardno`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk AUTO_INCREMENT=12000 ;--
-- 导出表中的数据 `tmpcard`
-- INSERT INTO `tmpcard` VALUES (1000, 'MCSD-24435446-DMOL', 0000);
INSERT INTO `tmpcard` VALUES (1001, 'MCSD-64928682-XLHG', 0000);
INSERT INTO `tmpcard` VALUES (1002, 'MCSD-66842232-TVTE', 0000);
INSERT INTO `tmpcard` VALUES (1003, 'MCSD-98886021-NDBO', 0000);
INSERT INTO `tmpcard` VALUES (1004, 'MCSD-34884824-NOVR', 0000);
INSERT INTO `tmpcard` VALUES (1005, 'MCSD-02859846-TCWX', 0000);
INSERT INTO `tmpcard` VALUES (1006, 'MCSD-48767680-ZRDH', 0000);
INSERT INTO `tmpcard` VALUES (1007, 'MCSD-08674964-XJGL', 0000);
INSERT INTO `tmpcard` VALUES (1008, 'MCSD-12174666-AFPP', 0000);
INSERT INTO `tmpcard` VALUES (1009, 'MCSD-68114435-IEHR', 0000);
INSERT INTO `tmpcard` VALUES (1010, 'MCSD-28866022-CESY', 0000);
INSERT INTO `tmpcard` VALUES (1011, 'MCSD-00306645-NNVV', 0000);
INSERT INTO `tmpcard` VALUES (1012, 'MCSD-46922454-AGEV', 0000);
INSERT INTO `tmpcard` VALUES (1013, 'MCSD-42266542-BECQ', 0000);
INSERT INTO `tmpcard` VALUES (1014, 'MCSD-34480042-VEQP', 0000);
INSERT INTO `tmpcard` VALUES (1015, 'MCSD-42682244-BULG', 0000);
当以上数据执行SQL语句:LIMIT 10SELECT GROUP_CONCAT(cardno separator '\\r\\n') FROM tmpcard WHERE uid < 1 ORDER BY cardid ASC LIMIT 10可以的,但是导出大量的时候,比如LIMIT 1000的时候就不行了。总是省略掉,而且只能导出 十几行。
如何解决啊。
表结构:mysql> desc tmpcard;
+--------+--------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------------+------+-----+---------+----------------+
| cardid | int(11) | NO | PRI | NULL | auto_increment |
| cardno | char(64) | NO | UNI | 0 | |
| uid | int(4) unsigned zerofill | NO | | 0000 | |
+--------+--------------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
需求:用导出前1000条数据成为文本文件。
SQL语句:SELECT GROUP_CONCAT(cardno separator '\\r\\n') FROM tmpcard WHERE uid < 1 ORDER BY cardid ASC LIMIT 1000 ; 但是奇怪的事情是,每次导出来只有一点点,而且末尾的的最后的那一个只有一半数据。测试数据:--
-- 表的结构 `tmpcard`
-- DROP TABLE IF EXISTS `tmpcard`;
CREATE TABLE IF NOT EXISTS `tmpcard` (
`cardid` int(11) NOT NULL auto_increment,
`cardno` char(64) NOT NULL default '0',
`uid` int(4) unsigned zerofill NOT NULL default '0000',
PRIMARY KEY (`cardid`),
UNIQUE KEY `newcard` (`cardno`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk AUTO_INCREMENT=12000 ;--
-- 导出表中的数据 `tmpcard`
-- INSERT INTO `tmpcard` VALUES (1000, 'MCSD-24435446-DMOL', 0000);
INSERT INTO `tmpcard` VALUES (1001, 'MCSD-64928682-XLHG', 0000);
INSERT INTO `tmpcard` VALUES (1002, 'MCSD-66842232-TVTE', 0000);
INSERT INTO `tmpcard` VALUES (1003, 'MCSD-98886021-NDBO', 0000);
INSERT INTO `tmpcard` VALUES (1004, 'MCSD-34884824-NOVR', 0000);
INSERT INTO `tmpcard` VALUES (1005, 'MCSD-02859846-TCWX', 0000);
INSERT INTO `tmpcard` VALUES (1006, 'MCSD-48767680-ZRDH', 0000);
INSERT INTO `tmpcard` VALUES (1007, 'MCSD-08674964-XJGL', 0000);
INSERT INTO `tmpcard` VALUES (1008, 'MCSD-12174666-AFPP', 0000);
INSERT INTO `tmpcard` VALUES (1009, 'MCSD-68114435-IEHR', 0000);
INSERT INTO `tmpcard` VALUES (1010, 'MCSD-28866022-CESY', 0000);
INSERT INTO `tmpcard` VALUES (1011, 'MCSD-00306645-NNVV', 0000);
INSERT INTO `tmpcard` VALUES (1012, 'MCSD-46922454-AGEV', 0000);
INSERT INTO `tmpcard` VALUES (1013, 'MCSD-42266542-BECQ', 0000);
INSERT INTO `tmpcard` VALUES (1014, 'MCSD-34480042-VEQP', 0000);
INSERT INTO `tmpcard` VALUES (1015, 'MCSD-42682244-BULG', 0000);
当以上数据执行SQL语句:LIMIT 10SELECT GROUP_CONCAT(cardno separator '\\r\\n') FROM tmpcard WHERE uid < 1 ORDER BY cardid ASC LIMIT 10可以的,但是导出大量的时候,比如LIMIT 1000的时候就不行了。总是省略掉,而且只能导出 十几行。
如何解决啊。
估计是这个问题,设置大点看看
修改你的 group_concat_max_len
我现在用的是PHP累加的。还是没打算用这个GROUP_CONCAT了,不想修改MYSQL的配置,可能会导致错误吧。