MySQL create command SELECT/INSERT/UPDATE
Par PlaceOweb le dimanche, janvier 18 2015, 23:30 - SQL - Lien permanent
Commandes SQL pour générer des statements SELECT, INSERT, REPLACE, UPDATE de vos tables, dans le but d'avoir les requêtes déjà prêtes avec la liste de vos champs.
/*
`information_schema`.`COLUMNS`
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
ORDINAL_POSITION
COLUMN_DEFAULT
IS_NULLABLE
DATA_TYPE
CHARACTER_MAXIMUM_LENGTH
CHARACTER_OCTET_LENGTH
NUMERIC_PRECISION
NUMERIC_SCALE
CHARACTER_SET_NAME
COLLATION_NAME
COLUMN_TYPE
COLUMN_KEY
EXTRA
PRIVILEGES
COLUMN_COMMENT
*/
-- SET [SESSION | GLOBAL] group_concat_max_len (default 1024)
SET SESSION group_concat_max_len = 10240;
SET @schema = 'ici_ma_base';
SET @table = 'ici_ma_table';
-- SELECT @schema, @table;
SELECT *
FROM `information_schema`.`COLUMNS`
WHERE TABLE_SCHEMA = 'nav_scot_sotranasa'
AND TABLE_NAME = 'attachement_pidi'
ORDER BY ORDINAL_POSITION ASC
;
-- SELECT *
SELECT CONCAT(
'SELECT ',
GROUP_CONCAT(COLUMN_NAME SEPARATOR ', '),
' FROM ',
TABLE_NAME
) AS 'SELECT_ALL_FIELD_FROM_TABLE'
FROM `information_schema`.`COLUMNS`
WHERE TABLE_SCHEMA = @schema
AND TABLE_NAME = @table
ORDER BY ORDINAL_POSITION ASC
;
-- INSERT
-- REPLACE
SELECT CONCAT(
'INSERT INTO ',
TABLE_NAME,
' (',
GROUP_CONCAT(COLUMN_NAME SEPARATOR ', '),
') VALUES (',
GROUP_CONCAT(COLUMN_NAME SEPARATOR ', '),
')'
) AS 'INSERT_ALL_FIELD_FROM_TABLE'
FROM `information_schema`.`COLUMNS`
WHERE TABLE_SCHEMA = @schema
AND TABLE_NAME = @table
ORDER BY ORDINAL_POSITION ASC
;
-- UPDATE
SELECT CONCAT(
'UPDATE ',
TABLE_NAME,
' SET ',
GROUP_CONCAT( CONCAT(COLUMN_NAME, ' = ', COLUMN_NAME) SEPARATOR ', '),
' WHERE 0'
) AS 'SELECT_ALL_FIELD_FROM_TABLE'
FROM `information_schema`.`COLUMNS`
WHERE TABLE_SCHEMA = @schema
AND TABLE_NAME = @table
ORDER BY ORDINAL_POSITION ASC
;