Geek Answers Handbook

MySql: work on many lines using a long list of composite PCs

What is a good way to work with many rows in MySql, given that I have a long list of keys in a client application that connects to ODBC?

Note: my experience is mostly SQL Server, so I know a little, not just MySQL.

The challenge is to delete multiple rows from 9 tables, but I can have more than 5000 key pairs.

I started with a simple way to scroll through all of my keys and send an operator for each of them on each table, for example:

DELETE FROM Table WHERE Key1 = 123 AND Key2 = 567 -- and 8 more tables
DELETE FROM Table WHERE Key1 = 124 AND Key2 = 568 -- and 8 more tables
DELETE FROM Table WHERE Key1 = 125 AND Key2 = 569 -- and 8 more tables
...

In addition, it goes beyond 45,000 individual operators, which, as you can imagine, are a bit slow.

So, without worrying about the programming language that I use in the interface, what is a good way to send a list so that I can JOIN and perform the operation right away, or at least in large batches? Here are my ideas:

  • Create a temp table and paste into it, then join. I will gladly review the syntax for MySQL to create a temporary table, but is this a good route?

  • , , ? 5000 INSERT Table VALUES () ? SELECT 123, 456 UNION ALL SELECT 124, 457? , MySql SELECT, . SQL Server , , MySQL? ?

    --CREATE Temp Table ( I do not know the syntax in MySql yet)

INSERT INTO TempTable
SELECT 123, 456
UNION ALL SELECT 124, 457
UNION ALL SELECT 125, 458

DELETE T
FROM
   Table T
   INNER JOIN TempTable X ON T.Key1 = X.Key1 AND T.Key2 = X.Key2

  • XML. , MySQL 5.1 XML, , , XML- . ? XML.

  • split. , MySql - . SQL Server , :

    CREATE PROCEDURE DoStuff @KeyString varchar(max)
AS
DECLARE @Keys TABLE (
   Key1 int,
   Key2 int,
   PRIMARY KEY CLUSTERED (Key1, Key2)
)
DECLARE @Pos int
WHILE @Pos < Len(@KeyString) BEGIN
   -- loop to search for delimiting commas in @KeyString
   -- and insert pairs of parsed tokens to table variable @Keys
END

DELETE T
FROM
   Table T
   INNER JOIN @Keys K ON T.Key1 = K.Key1 AND T.Key2 = K.Key2

MySQL, , , , / .

+5
1

temp table DELETE. , , .

  • CREATE TEMPORARY TABLE

    CREATE TEMPORARY TABLE Keys (
    Key1 INT UNSIGNED NOT NULL, 
    Key2 INT UNSIGNED NOT NULL, 
    PRIMARY KEY(Key1, Key2)
);

  • temp, LOAD DATA LOCAL INFILE

    LOAD DATA LOCAL INFILE 'C:/path/to/datafile' INTO TABLE Keys;

  • MySQL multi-table DELETE .

    DELETE t FROM Table1 t JOIN Keys USING (Key1, Key2);
DELETE t FROM Table2 t JOIN Keys USING (Key1, Key2);
DELETE t FROM Table3 t JOIN Keys USING (Key1, Key2);
DELETE t FROM Table4 t JOIN Keys USING (Key1, Key2);
DELETE t FROM Table5 t JOIN Keys USING (Key1, Key2);
DELETE t FROM Table6 t JOIN Keys USING (Key1, Key2);
DELETE t FROM Table7 t JOIN Keys USING (Key1, Key2);
DELETE t FROM Table8 t JOIN Keys USING (Key1, Key2);
DELETE t FROM Table9 t JOIN Keys USING (Key1, Key2);

:

MySQL CREATE TABLE :

TEMPORARY . , , . ( , .)

!

, INSERT. 5000 . PHP (, StackOverflow XML) MySQL, 20 . , .

+2

More articles:


All Articles