当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Best way to insert and query a lot of data MySQL

问题描述:

I have to read approximately 5000 rows of 6 columns max from a xls file. I'm using PHP >= 5.3. I'll be using PHPExcel for that task. I haven't try it but I think it can handle (If you have other options, they are welcome).

The issue is that every time I read a row, I need to query the database to verify if that particular row exists, If it does, then overwrite it, If not, then add it.

I think that's going to take a lot of time and PHP will just simply timeout ( I can't modify the timeout variable since it's a shared server).

Could you give me a hand with this?

Appreciate your help

网友答案:

Since you're using MySQL, all you have to do is insert data and not worry about a row being there at all. Here's why and how:

  • If you query a database from PHP to verify a row exists, that's bad. Reason it's bad is because you are prone to getting false results. There's a lag between PHP and MySQL, and PHP can't be used to verify data integrity. That's the job of the database.

  • To ensure there are no duplicate rows, we use UNIQUE constraints on our columns.

  • MySQL extends SQL standard using INSERT INTO ... ON DUPLICATE KEY UPDATE syntax. That lets you just insert data, and if there's a duplicate row - you can just update it with new data.

  • Reading 5000 rows is quick. Inserting 5000 is also quick, if you wrap it in a transaction. I would suggest reading 100 rows from the excel file, starting a transaction and just insert data (using ON DUPLICATE KEY UPDATE to handle duplicates). That will let you spend 1 I/O of your hard drive to save 100 records. Doing so, you can finish this whole process in a few seconds, which lets you not to worry about performance or timeouts.

网友答案:

At first run this process via exec, and timeout has no matter
At second, select all rows before read excel file. Select not at one query, read 2000 rows at time for example, and collect it to array.
At third use xlsx format and chunkReader, that allows read not whole file. It's not 100% garantee, but i did the same.

分享给朋友:
您可能感兴趣的文章:
随机阅读: