CODEWITHSUNDEEP
mysqlsql
supercoolville
supercoolville

Reputation: 9096

How do I execute a lot of SQL queries without problems?

I have an SQL file with 30,000 UPDATE lines. When I upload it through phpmyadmin it freezes at a certain point and doesn't update everything.

Is there a way to execute all 30,000 lines, without problems, all at once? or do I have to go through and manually execute 200 lines at a time?

Line Example:

UPDATE `table` SET `value1`='Some text', `value2`=0 `value3`=1 WHERE id=500;

^ I have 30,000 lines like that.

Upvotes: 1

Views: 162

Answers (1)

Milan Babuškov
Milan Babuškov

Reputation: 61198

PHPMyAdmin's query parsing is slow. It's much better to log into server via SSH and execute the command using mysql client:

$ mysql -uUsername -pPassword DatabaseName < script.sql

If you don't have SSH access, you can upload the sql script (via FTP, for example) and write a small PHP script that calls the command using system, exec or similar PHP function:

<?php
system('mysql -uUsername -pPassword DatabaseName < script.sql');

Then invoke the script via browser.

Make sure you use full paths to mysql (/usr/bin/mysql usually) and your script file.

If you use non-system character set, make sure you add the default_character_set option as well.

Upvotes: 5

Related Questions