TABLE 정보 조회 및 난수 생성

 

 -- uuid 생성 yyyyMMddHHmissSSS+난수3자리 - total 20자리

SELECT CONCAT(SUBSTR(DATE_FORMAT(NOW(3), "%Y%m%d%H%i%s%f"), 1, 17), LPAD(FLOOR(RAND() * 1000), 3, '0'));

 

 -- TABLE 정보 조회
SELECT
    -- COUNT(*)
     TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE, EXTRA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='데이터베이스명'
-- AND TABLE_NAME='테이블명'
-- AND DATA_TYPE = 'CHAR'
-- AND COLUMN_NAME LIKE '%SEQ'
AND DATA_TYPE                = 'VARCHAR'
AND CHARACTER_MAXIMUM_LENGTH = '21'
ORDER BY TABLE_NAME, COLUMN_NAME
-- ORDER BY ORDINAL_POSITION
;