Mateusz Kimont

---

Mysql 5.7 Notes

---

SET @your_variable = 'your_value';
SET @collation_variable = 'utf8mb4_unicode_ci';
SET @sql = CONCAT('SELECT * FROM your_table WHERE your_column = ? COLLATE ', @collation_variable);
PREPARE stmt FROM @sql;
EXECUTE stmt USING @your_variable;
DEALLOCATE PREPARE stmt;

SET @coll = (SELECT COLLATION(column_name) AS column_collation FROM table_name WHERE column_name = 'column_value' LIMIT 1);
SET @newFileLocation = 'dir/imageName.jpg';
# need to use string as variable doesnt work as expected
SET @newLocation = CONCAT('dir/imageName.jpg', @coll);
SET @newLocation =  SUBSTRING(@newLocation, 1, LENGTH(@newFileLocation));

Split delimited strings

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

SELECT SPLIT_STR(string, delimiter, position)