我需要将数据从 mysql 导出到 csv。我必须从多个表中选择数据,将它们放入数组中,然后处理它们并将它们作为 .csv 返回到浏览器。
我注意到数组消耗了大量的行。
例如,我在数据库中导入了一个1.8M的.csv,然后我尝试从数据库中以.csv导出此数据。 memory_get_peak_usage() 显示超过 128M 来存储带有数据的数组。
例如这个小数组需要超过 700 个字节:
$startMemory = memory_get_usage();
//get constant fields of the subscriber
$data = array(array('subscriber_id' => 1315444, 'email_address' => '[email protected]',
'first_name' => 'Michael', 'last_name' => 'Allen'));
echo memory_get_usage() - $startMemory;
因此,即使导出几兆字节的数据,php 脚本也需要数百兆字节的内存。
有办法解决这个问题吗?
表格:
CREATE TABLE `subscribers` (
`subscriber_id` int(10) unsigned NOT NULL auto_increment,
`list_id` int(10) unsigned NOT NULL,
`account_id` int(10) unsigned NOT NULL,
`email_address` varchar(100) collate utf8_unicode_ci NOT NULL,
`first_name` varchar(50) collate utf8_unicode_ci NOT NULL default '',
`last_name` varchar(50) collate utf8_unicode_ci NOT NULL default '',
`ip` int(10) unsigned default NULL COMMENT '\nThe ip address of the subscriber that we can get when he opens the \nthe email or subscribe using subsribe form.\nTheoretically it can be used to segment by Location (which is not correct if someone uses proxy).',
`preferred_format` tinyint(4) NOT NULL default '0' COMMENT 'Preferred format of \n0 - HTML, \n1 -Text,\n2 - Mobile',
`state` tinyint(4) NOT NULL default '1' COMMENT '1 - subscribed\n2 - unsubscribed\n3 - cleaned\n4 - not confirmed, it means the user subscribed but has not confirmed it yet.\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n',
`cause_of_cleaning` tinyint(4) NOT NULL default '0' COMMENT '\nThis field is the cause of moving the subscriber to the \n0 - not used\n1 - spam complaint\n2 - hard bounce\n3 - several soft bounces',
`date_added` datetime NOT NULL COMMENT 'The data when the subscriber was added. I suppose this field can be used in the conditions forming the segment',
`last_changed` datetime NOT NULL,
PRIMARY KEY (`subscriber_id`),
UNIQUE KEY `email_list_id` (`email_address`,`list_id`),
KEY `FK_list_id` (`list_id`),
CONSTRAINT `FK_list_id` FOREIGN KEY (`list_id`) REFERENCES `lists` (`list_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
CREATE TABLE `subscribers_multivalued` (
`id` int(10) unsigned NOT NULL auto_increment,
`subscriber_id` int(10) unsigned NOT NULL,
`field_id` int(10) unsigned NOT NULL,
`value` varchar(100) collate utf8_unicode_ci NOT NULL,
`account_id` int(10) unsigned NOT NULL COMMENT '\nThe identifier of the account',
PRIMARY KEY (`id`),
KEY `subscriber_fk` (`subscriber_id`),
KEY `field_fk` (`field_id`),
CONSTRAINT `field_fk_string_multivalued` FOREIGN KEY (`field_id`) REFERENCES `custom_fields` (`field_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `subscriber_fk_multivalued` FOREIGN KEY (`subscriber_id`) REFERENCES `subscribers` (`subscriber_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
CREATE TABLE `subscribers_custom_data_string` (
`subscriber_id` int(10) unsigned NOT NULL,
`field_id` int(10) unsigned NOT NULL,
`value` varchar(255) collate utf8_unicode_ci NOT NULL,
`account_id` int(10) unsigned NOT NULL COMMENT '\nThe identifier of the account',
PRIMARY KEY (`subscriber_id`,`field_id`),
KEY `subscriber_fk` (`subscriber_id`),
KEY `field_fk` (`field_id`),
CONSTRAINT `field_fk_string` FOREIGN KEY (`field_id`) REFERENCES `custom_fields` (`field_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `subscriber_fk_string` FOREIGN KEY (`subscriber_id`) REFERENCES `subscribers` (`subscriber_id`) ON DELETE CASCADE ON UPDATE CASCADE
)
还有其他字段表类似于带有数字、日期字符串的表。对于他们来说,主键是subscriber_id、field_id。
当查询失败时(例如我们有几个自定义字段):
SELECT subscribers
.email_address
, subscribers
.first_name
, subscribers
.last_name
,
GROUP_CONCAT(t1.value SEPARATOR '|') AS 颜色,GROUP_CONCAT(t2.value SEPARATOR '|') AS 语言
从subscribers
左连接subscribers_multivalued
AS t1
ON 订阅者.subscriber_id=t1.subscriber_id AND t1.field_id=112
左连接subscribers_multivalued
AS t2
ON 订阅者.subscriber_id=t2.subscriber_id AND t2.field_id=111
哪里(list_id=40)
通过...分组subscribers
.email_address
, subscribers
.first_name
, subscribers
.last_name
它会返回这个:
[电子邮件受保护]米歇尔·布什 红色|红色|蓝色|蓝色 英语|西班牙语|英语|西班牙语 而不是
测试[电子邮件受保护]米歇尔·布什 红|蓝
英语|西班牙语
感谢您提供任何信息。