-
Unlocking Simplicity: Setting Up REST Service for MySQL HeatWave
Today’s developers strive for agility without sacrificing security or performance, especially when building data-driven apps and AI solutions. That’s why we’re excited to introduce the MySQL HeatWave REST Service (MRS), a fully managed, integrated solution that lets you create RESTful API endpoints instantly, directly from your MySQL HeatWave databases. There’s no need for backend coding, middleware, or third-party frameworks to provide streamlined and secure access to your data over HTTPS.
-
Beyond EOL: The Real Benefits of Upgrading to MySQL 8.4
Right now, you’re probably hoping someone else will deal with this MySQL 8.0 end-of-life situation. Maybe your team can squeeze another few months out of it. Maybe Oracle will extend support. Maybe it won’t be as bad as everyone says. We get it. You’ve got enough things going on without adding “major database upgrade” to […]
-
MySQL: Flag Codes from Country Codes
Regional Indicator Symbols
in Unicode are the codes starting at U+1F1E6 to U+1F1FF.
If you combine two of them in a valid ISO-3166-1 alpha-2
codes,
they produce the flag corresponding to that code.
We want a function flag_emoji() that takes such a two-letter code and emits the appropriate Unicode codepoint:
SELECT flag_emoji('GB') AS gb, flag_emoji('us') AS us, flag_emoji('de') AS de \G
gb: 🇬🇧
us: 🇺🇸
de: 🇩🇪
1 row in set (0.00 sec)
MySQL
For that, we define a helper function unichar() that makes us a Unicode character from a codepoint (INT),
using the Unicode algorithm and UNHEX() plus CONVERT().
-- Make sure your session speaks utf8mb4
SET NAMES utf8mb4;
-- helper: convert a Unicode code point to a utf8mb4 string using raw bytes
DELIMITER //
DROP FUNCTION IF EXISTS unichar//
CREATE FUNCTION unichar(cp INT)
RETURNS VARCHAR(4) CHARSET utf8mb4
DETERMINISTIC
BEGIN
DECLARE b1 INT; DECLARE b2 INT; DECLARE b3 INT; DECLARE b4 INT;
IF cp <= 0x7F THEN
SET b1 = cp;
RETURN CONVERT(UNHEX(LPAD(HEX(b1),2,'0')) USING utf8mb4);
ELSEIF cp <= 0x7FF THEN
SET b1 = 0xC0 | (cp >> 6);
SET b2 = 0x80 | (cp & 0x3F);
RETURN CONVERT(CONCAT(
UNHEX(LPAD(HEX(b1),2,'0')),
UNHEX(LPAD(HEX(b2),2,'0'))
) USING utf8mb4);
ELSEIF cp <= 0xFFFF THEN
SET b1 = 0xE0 | (cp >> 12);
SET b2 = 0x80 | ((cp >> 6) & 0x3F);
SET b3 = 0x80 | (cp & 0x3F);
RETURN CONVERT(CONCAT(
UNHEX(LPAD(HEX(b1),2,'0')),
UNHEX(LPAD(HEX(b2),2,'0')),
UNHEX(LPAD(HEX(b3),2,'0'))
) USING utf8mb4);
ELSE
SET b1 = 0xF0 | (cp >> 18);
SET b2 = 0x80 | ((cp >> 12) & 0x3F);
SET b3 = 0x80 | ((cp >> 6) & 0x3F);
SET b4 = 0x80 | (cp & 0x3F);
RETURN CONVERT(CONCAT(
UNHEX(LPAD(HEX(b1),2,'0')),
UNHEX(LPAD(HEX(b2),2,'0')),
UNHEX(LPAD(HEX(b3),2,'0')),
UNHEX(LPAD(HEX(b4),2,'0'))
) USING utf8mb4);
END IF;
END//
DELIMITER ;
We can now use that to produce regional indicators from normal ASCII:
-- flag function, idempotent create
DELIMITER //
DROP FUNCTION IF EXISTS flag_emoji//
CREATE FUNCTION flag_emoji(cc VARCHAR(2))
RETURNS VARCHAR(8) CHARSET utf8mb4
DETERMINISTIC
BEGIN
DECLARE base INT DEFAULT 127462; -- U+1F1E6 REGIONAL INDICATOR LETTER A
IF cc IS NULL OR cc NOT REGEXP '^[A-Za-z]{2}$' THEN
RETURN NULL;
END IF;
RETURN CONCAT(
unichar(base + ASCII(UPPER(SUBSTRING(cc,1,1))) - ASCII('A')),
unichar(base + ASCII(UPPER(SUBSTRING(cc,2,1))) - ASCII('A'))
);
END//
DELIMITER ;
Test this code:
-- test
SELECT flag_emoji('GB') AS gb, flag_emoji('us') AS us, flag_emoji('de') AS de\G
Postgres
-- Requires database encoding UTF8
CREATE OR REPLACE FUNCTION flag_emoji(code text)
RETURNS text
LANGUAGE sql
IMMUTABLE
STRICT
PARALLEL SAFE
AS $$
SELECT CASE
WHEN code ~ '^[A-Za-z]{2}$' THEN
-- U+1F1E6 (decimal 127462) is the Regional Indicator "A"
chr(127462 + ascii(upper(substr(code, 1, 1))) - 65) ||
chr(127462 + ascii(upper(substr(code, 2, 1))) - 65)
ELSE NULL
END;
$$;
-- Example
SELECT
flag_emoji('GB') AS gb,
flag_emoji('us') AS us,
flag_emoji('de') AS de;
and test with
SELECT flag_emoji('GB') AS gb, flag_emoji('us') AS us, flag_emoji('de') AS de;
Postgres does not need the helper function to work around the CONVERT() issues that MySQL has.
-
Using MySQL HeatWave as a knowledge base with the OCI AI Agent Platform
How to use MySQL HeatWave’s vector store as a knowledge base when using OCI’s Generative AI Agent Service
-
Introducing Natural Language to SQL for MySQL HeatWave
Announcing the release of a new Natural Language to SQL (NL2SQL) feature for MySQL HeatWave.
This innovative capability enables users to easily convert questions expressed in natural language into SQL queries, simplifying data analysis and accelerating insights.
|