Last active
May 10, 2019 08:36
-
-
Save lkebin/5c1344fc10af026d154f21675a59535a to your computer and use it in GitHub Desktop.
MySQL table schema to Golang struct
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DELIMITER $$ | |
CREATE PROCEDURE `tableGoStruct`( | |
IN dbName VARCHAR(50) , | |
IN tableName VARCHAR(50) | |
) | |
BEGIN | |
DECLARE struct LONGTEXT; | |
DECLARE structType VARCHAR(10); | |
DECLARE colName VARCHAR(64); | |
DECLARE dataType VARCHAR(64); | |
DECLARE colType LONGTEXT; | |
DECLARE done INT DEFAULT FALSE; | |
DECLARE c CURSOR FOR SELECT column_name, data_type, column_type FROM information_schema.COLUMNS WHERE table_name = tableName AND table_schema = dbName; | |
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; | |
SET struct = concat("type ", tableName, " struct {\n"); | |
OPEN c; | |
read_loop: LOOP | |
FETCH c INTO colName, dataType, colType; | |
IF done THEN | |
LEAVE read_loop; | |
END IF; | |
SET structType = CASE | |
WHEN dataType IN('int', 'tinyint', 'smallint') AND locate('unsigned', colType) = 0 THEN 'int' /* int -> int */ | |
WHEN dataType IN('int', 'tinyint', 'smallint') AND locate('unsigned', colType) > 0 THEN 'uint' /* int unsigned -> uint */ | |
WHEN dataType = 'bigint' AND locate('unsigned', colType) = 0 THEN 'int64' /* bigint -> int64 */ | |
WHEN dataType = 'bigint' AND locate('unsigned', colType) > 0 THEN 'uint64' /* bigint unsigned -> uint64 */ | |
ELSE 'string' | |
END; | |
SET struct = concat(struct, "\t", colName, " ", structType, "\n"); | |
END LOOP; | |
CLOSE c; | |
SET struct = concat(struct, "}"); | |
SELECT struct; | |
END$$ | |
DELIMITER ; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment