0

I am importing with mysqlimport (batch file) records to a specific table every 5 minutes, in order to get in this table online guests on my website.

I currently send 2 .sql executed on a Windows batch file to a remote database: 1 SQL command to drop and create table and then a second one with mysqlimport to populate table with CSV.

I was wondering if a better way would not be to send a mysqlimport command from my batch file and have a trigger on database upon insert on this specific DB to drop it and recreate it before inserting new records, in order to avoid a gap (no records) currently happening between the moment when table is dropped, recreated and then populated with the CSV, which happens a lot.

Any idea how to implement such a trigger?

Structure of the table online_players table:

+-------------------+--------------+------+-----+---------+-------+
| Agent             | varchar(255) | YES  |     | NULL    |       |
| Name              | varchar(255) | YES  |     | NULL    |       |
| Alias             | varchar(255) | YES  |     | NULL    |       |
| Begin_Date        | varchar(100) | YES  |     | NULL    |       |
| LastBalanceUpdate | varchar(100) | YES  |     | NULL    |       |
| Session_minutes   | varchar(100) | YES  |     | NULL    |       |
| Balance           | varchar(100) | YES  |     | NULL    |       |
| Product           | varchar(100) | YES  |     | NULL    |       |
+-------------------+--------------+------+-----+---------+-------+
halfer
  • 19,824
  • 17
  • 99
  • 186
R_life_R
  • 786
  • 6
  • 26
  • Do you have a unique key defined for this table? If so, you should be able to use the `replace` option of `mysqlimport` on the unique key without having to drop-recreate the table each time you need to reload with a new CSV file.. – vmachan Apr 03 '16 at 17:22
  • No but I can set one up .I understand that rows present before import be deleted by the replace ? without need to drop table? – R_life_R Apr 03 '16 at 19:17
  • i finally used the -d or --delete parameter I did not know about and deletes all rows in table before inserting. Thanks for your help! – R_life_R Apr 03 '16 at 19:30

1 Answers1

0

I solved the issue of droping / creating table before each insert by using the -d parameter in mysqlimport which deletes all rows before importing csv. Thanks vmachan for your help!

R_life_R
  • 786
  • 6
  • 26