Menu
I am using MYSQL 5.7 and I do not have an access to methods introduced in 8.0.
Therefore I'd like to do something like this:
CREATE FUNCTION uuid_to_bin(uuid CHAR(36))
RETURNS BINARY(16) DETERMINISTIC
RETURN concat(
substr(unhex(replace(uuid, '-', '')), 7, 2),
substr(unhex(replace(uuid, '-', '')), 5, 2),
substr(unhex(replace(uuid, '-', '')), 1, 4),
substr(unhex(replace(uuid, '-', '')), 9, 8));
CREATE TABLE `example` (
`id` BINARY(16) GENERATED ALWAYS AS (uuid_to_bin(document->>'$.id')) STORED NOT NULL,
`document` JSON NOT NULL,
PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8
COLLATE = utf8_bin;
However when i run given sql it throws an error:
[2017-11-01 14:12:41] [HY000][3102] Expression of generated column 'id' contains a disallowed function.
Even thought the function is deterministic. How can I do that ?
I called my function uuid_to_bin because if in the future I'll be able to upgrade my MYSQL server to 8.0 I do not want to change the sql for my columns
Also maybe this be a reason ? COLLATION 'utf8_bin' is not valid for CHARACTER SET 'binary'
298
MySQL generated column's syntax First, specify the column name and its data type. Next, add the GENERATED ALWAYS clause to indicate that the column is a generated column. Then, indicate whether the type of the generated column by using the corresponding option: VIRTUAL or STORED .
MySQLMySQLi Database. Basically generated columns are a feature that can be used in CREATE TABLE or ALTER TABLE statements and is a way of storing the data without actually sending it through the INSERT or UPDATE clause in SQL. This feature has been added in MySQL 5.7. A generated column works within the table domain.
Introduction. “Generated Always Column”: are columns, table fields, filled by DB2 engine: something like columns with a default value but in this case always with the default value not only when users don't pass a value.
To create a generated column, use the GENERATED ALWAYS AS clause in CREATE TABLE , for example: CREATE TABLE people ( ..., height_cm numeric, height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED ); The keyword STORED must be specified to choose the stored kind of generated column.
MySQLMySQLi Database. Basically generated columns is a feature which can be used in CREATE TABLE or ALTER TABLE statements and is a way of storing the data without actually sending it through the INSERT or UPDATE clause in SQL. This feature has been added in MySQL 5.7.
If you are using the Databricks environment, this feature is available on Databricks Runtime 8.3 and above. What are Generated Columns? Generated Columns are a special type of columns whose values are automatically generated based on user-specified functions over the columns in Delta Table.
The syntax for defining a generated column is as follows: First, specify the column name and its data type. Next, add the GENERATED ALWAYS clause to indicate that the column is a generated column. Then, indicate whether the type of the generated column by using the corresponding option: VIRTUAL or STORED.
By using the MySQL generated column, you can recreate the contacts table as follows: The GENERATED ALWAYS as (expression) is the syntax for creating a generated column. To test the fullname column, you insert a row into the contacts table. Now, you can query data from the contacts table.
https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html - Generated column expressions must adhere to the following rules. An error occurs if an expression contains disallowed constructs.
Literals, deterministic built-in functions, and operators are permitted.. Subqueries, parameters, variables, stored functions, and user-defined functions are not permitted.
You may not find the last one in verbatim in the documentation. However, CREATE FUNCTION page says
The CREATE FUNCTION statement is used to create stored functions and loadable functions
While the other page above explicitly says:
Stored functions and loadable functions are not permitted.
65
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
