CODEWITHSUNDEEP
mysqlarraysstore
user1815934
user1815934

Reputation: 229

Store a big array in mySQL

what is the best way to store a big array into a mySQL DB?

First solution

INSERT INTO `friendList` ( `userId` , `friendId` , `friendName` ) VALUES 
    ( '1', '3242343', 'bernd' ), 
    ( '1', '3242342', 'jusu' );

or with foreach

second solution

foreach (xxxx) {
     mysql_query("insert xxxx");
}

The first solution is better (cleaner and not so burdensome for the server, right?), but how do a create this insert command?

Upvotes: 0

Views: 1208

Answers (2)

Ja͢ck
Ja͢ck

Reputation: 173662

The fastest would be to use the multiple insert syntax (as you've described), but that's not what I would recommend to most people unless performance is truly vital. You also need to take care not to exceed the maximum query size (which can change between servers).

However, you could do this with prepared statements (PDO example) and still get reasonable performance:

$db = new PDO($dsn, $user, $password, array(
    PDO::ATTR_EMULATE_PREPARES => false,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
));

$stmt = $db->prepare('INSERT INTO `friendList` ( `userId` , `friendId` , `friendName` ) VALUES (?, ?, ?)');

foreach ($data as $row) {
    $stmt->execute(array($row['id'], $row['friendid'], $row['friendname']));
}

Upvotes: 1

ke20
ke20

Reputation: 665

Store all data in a csv file or a text file and use LOAD DATA INFILE

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
    [TERMINATED BY '\t']
    [[OPTIONALLY] ENCLOSED BY '']
    [ESCAPED BY '\\' ]
]
[LINES 
    [STARTING BY '']    
    [TERMINATED BY '\n']
]
[IGNORE number LINES]
[(col_name,...)]

Upvotes: 1

Related Questions