xportação de uso de memória do banco de dados para csv em php

Question

Mar 15, 2011, 09:43 AM

xportação de uso de memória do banco de dados para csv em php

Preciso exportar dados do mysql para csv. Eu tenho que selecionar dados de várias tabelas, colocando-os em matrizes, processá-los e devolvê-los ao navegador como .csv. Notei que matrizes consomem uma quantidade enorme de linhas. Por exemplo, importei um .csv no banco de dados com 1,8 M e tento exportar esses dados do banco de dados em .csv. O memory_get_peak_usage () mostra mais de 128 milhões para armazenar matrizes com dado

Por exemplo, esta pequena matriz leva mais de 700 bytes:

$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;

Assim, a exportação de vários megabytes de dados exige centenas de megabytes de memória no script php. Existe alguma maneira de resolver este problema? Tabelas:

    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
)

Existem outras tabelas para campos semelhantes à tabela com cadeias de caracteres para números, datas. Para eles, a chave primária é subscriber_id, field_id.

Quando a consulta falha (por exemplo, temos vários campos personalizados):

SELECTsubscribers.email_address, subscribers.first_name, subscribers.last_name, GROUP_CONCAT (t1.value SEPARATOR '|') COMO Cores, GROUP_CONCAT (t2.value SEPARATOR '|') COMO Idiomas FROMsubscribers ASSOCIAÇÃO À ESQUERDAsubscribers_multivalued COMOt1 ON subscritores.subscriber_id = t1.subscriber_id AND t1.field_id = 112 JUNTA ESQUERDAsubscribers_multivalued COMOt2 ON subscription.subscriber_id = t2.subscriber_id AND t2.field_id = 111 ONDE (list_id = 40) GRUPO PORsubscribers.email_address, subscribers.first_name, subscribers.last_name