On gists

Prepared statements and another replace tricks

MySql MySql tricks MySql - advanced

example.sql Raw #

/*
 1/ standard way
*/
INSERT IGNORE INTO  product_transport_type (product_id, transport_type_id)
SELECT productId, transportId 
FROM (
    SELECT 
        p.id AS productId,
        t.transportId
    FROM product p
    CROSS JOIN (
        SELECT 6 AS transportId UNION 
        SELECT 7 UNION 
        SELECT 8 UNION 
        SELECT 9 UNION 
        SELECT 10 UNION 
        SELECT 13 UNION 
        SELECT 14 UNION 
        SELECT 15 UNION 
        SELECT 16 UNION 
        SELECT 17 UNION 
        SELECT 18 UNION 
        SELECT 20 UNION 
        SELECT 21 UNION 
        SELECT 23 UNION 
        SELECT 25 UNION 
        SELECT 26 UNION 
        SELECT 27 UNION 
        SELECT 28 UNION 
        SELECT 29
    ) t
    WHERE p.`code` IN (
        7230991, 9705946, 9710628, 9734100, 9734201, 9734500, 9740966, 9740967, 9740980, 
        9744605, 9744606, 9744631, 9744633, 9760507, 9760556, 9762549, 9762550, 9762551, 
        9762569, 9762570, 9762578, 9762639, 9762640, 9762641, 9762653, 9762689, 9762704, 
        9762707, 9763609, 9763617, 9763618, 9779064, 9779065, 9787016, 9787017
    )
) AS ProductsTransportsCombination;




/* 2 find in set - nepodporuje index - asi pomale / pomalejsi */
SET @transport_ids = '6,7,8,9,10,13,14,15,16,17,18,20,21,23,25,26,27,28,29';
SET @product_codes = '7230991,9705946,9710628,9734100,9734201,9734500,9740966,9740967,9740980,9744605,9744606,9744631,9744633,9760507,9760556,9762549,9762550,9762551,9762569,9762570,9762578,9762639,9762640,9762641,9762653,9762689,9762704,9762707,9763609,9763617,9763618,9779064,9779065,9787016,9787017';

INSERT IGNORE INTO product_transport_type (product_id, transport_type_id) 
SELECT p.id AS productId, t.transportId  
FROM product p 
CROSS JOIN ( 
    SELECT 6 AS transportId UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION 
    SELECT 10 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION 
    SELECT 17 UNION SELECT 18 UNION SELECT 20 UNION SELECT 21 UNION SELECT 23 UNION 
    SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29 
) t 
WHERE FIND_IN_SET(p.code, @product_codes)
  AND FIND_IN_SET(t.transportId, @transport_ids);
  
  
  
/* 3 prepared stm = rychlejsi kvuli pouzivani indexu */
SET @product_codes = '7230991,9705946,9710628,9734100,9734201,9734500,9740966,9740967,9740980,9744605,9744606,9744631,9744633,9760507,9760556,9762549,9762550,9762551,9762569,9762570,9762578,9762639,9762640,9762641,9762653,9762689,9762704,9762707,9763609,9763617,9763618,9779064,9779065,9787016,9787017';
SET @transport_ids = '6,7,8,9,10,13,14,15,16,17,18,20,21,23,25,26,27,28,29';

SET @sql = CONCAT('
INSERT IGNORE INTO product_transport_type (product_id, transport_type_id) 
SELECT p.id AS productId, t.transportId  
FROM product p 
CROSS JOIN ( 
    SELECT 6 AS transportId UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION 
    SELECT 10 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION 
    SELECT 17 UNION SELECT 18 UNION SELECT 20 UNION SELECT 21 UNION SELECT 23 UNION 
    SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29 
) t 
WHERE p.code IN (', @product_codes, ')
');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


/* 3b, state stm, ale sestaveni UNIONu dynamicky */
SET @product_codes = '7230991,9705946,9710628,9734100,9734201,9734500,9740966,9740967,9740980,9744605,9744606,9744631,9744633,9760507,9760556,9762549,9762550,9762551,9762569,9762570,9762578,9762639,9762640,9762641,9762653,9762689,9762704,9762707,9763609,9763617,9763618,9779064,9779065,9787016,9787017';
SET @transport_ids = '6,7,8,9,10,13,14,15,16,17,18,20,21,23,25,26,27,28,29';

-- Vytvoření UNION části pro transport IDs
SET @transport_union = REPLACE(REPLACE(@transport_ids, ',', ' UNION SELECT '), ',', '');
SET @transport_union = CONCAT('SELECT ', @transport_union);

SET @sql = CONCAT('
INSERT IGNORE INTO product_transport_type (product_id, transport_type_id) 
SELECT p.id AS productId, t.transportId  
FROM product p 
CROSS JOIN (', @transport_union, ') t 
WHERE p.code IN (', @product_codes, ')
');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;