'Study/MySQL' 카테고리의 글 목록

'Study/MySQL'에 해당되는 글 2건

2022.05.27 [MySql] json string을 파싱하여 쿼리 (조건, 반환)
2022.03.23 Add Column if not exists

Study/MySQL2022. 5. 27. 14:37

[MySql] json string을 파싱하여 쿼리 (조건, 반환)

SELECT uId, charId, dateKey, date, TRIM(BOTH '"' FROM ( json_extract(parameterString, '$.itemId') ))
, TRIM(BOTH '"' FROM ( json_extract(parameterString, '$.amount') ))
, parameterString FROM jsonlog WHERE dateKey = 20220526 and logid = 50701 AND parameterString LIKE '%itemRemoveType":"45"%';

Posted by 굥쓰

Study/MySQL2022. 3. 23. 14:24

Add Column if not exists

SELECT count(*) INTO @EXIST
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'column_name'
  AND TABLE_NAME = 'table_name' LIMIT 1;

SET @query = IF (@EXIST<= 0, 'ALTER TABLE `table_name`  ADD COLUMN `column_name` VARCHAR(100) NULL AFTER `isPublic`',
    'select \'column_name COLUMN EXISTS\' status');
PREPARE stmt FROM @query;
EXECUTE stmt;