Ziele Untersuchung
mit Columbo Integrität von
Datenbanken Interaktion und
Portierbarkeit Ergonomie der
Schnittstellen

Angebot Produkte Projekt Beratung

Mittel Analytik Modellierung Sprachen Algebra Logik Hardware Denken Kreativität

Zusammenhänge Gesellschaft Wirtschaft Branche Firma

Benutzer


products/Sources/formale Sprachen/C/MariaDB/mysql-test/suite/vcol/r/ (MariaDB Server Version 8.1-8.4^©) Datei vom 1.9.2026 mit Größe 84 kB

Quelle vcol_supported_sql_funcs.result Sprache: Lisp

SET @@session.default_storage_engine = 'MyISAM';
#
# NUMERIC FUNCTIONS
#
# ABS()
set sql_warnings = 1;
create table t1 (a int, b int as (abs(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (abs(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (-1, default);
select * from t1;
a b
-1 1
drop table t1;
set sql_warnings = 0;
# ACOS()
set sql_warnings = 1;
create table t1 (a double, b double as (format(acos(a),6)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double GENERATED ALWAYS AS (format(acos(`a`),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (1, default);
insert ignore into t1 values (1.0001,default);
insert into t1 values (0,default);
select * from t1;
a b
1 0
1.0001 NULL
0 1.570796
drop table t1;
set sql_warnings = 0;
# ASIN()
set sql_warnings = 1;
create table t1 (a double, b double as (format(asin(a),6)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double GENERATED ALWAYS AS (format(asin(`a`),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (0.2, default);
insert ignore into t1 values (1.0001,default);
select * from t1;
a b
0.2 0.201358
1.0001 NULL
drop table t1;
set sql_warnings = 0;
#ATAN
set sql_warnings = 1;
create table t1 (a double, b double, c double as (format(atan(a,b),6)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double DEFAULT NULL,
  `c` double GENERATED ALWAYS AS (format(atan(`a`,`b`),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (-2,2,default);
insert ignore into t1 values (format(PI(),6),0,default);
select * from t1;
a b c
-2 2 -0.785398
3.141593 0 1.570796
drop table t1;
set sql_warnings = 0;
set sql_warnings = 1;
create table t1 (a double, c double as (format(atan(a),6)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `c` double GENERATED ALWAYS AS (format(atan(`a`),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (-2,default);
insert ignore into t1 values (format(PI(),6),default);
select * from t1;
a c
-2 -1.107149
3.141593 1.262627
drop table t1;
set sql_warnings = 0;
# ATAN2
set sql_warnings = 1;
create table t1 (a double, b double, c double as (format(atan2(a,b),6)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double DEFAULT NULL,
  `c` double GENERATED ALWAYS AS (format(atan(`a`,`b`),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (-2,2,default);
insert ignore into t1 values (format(PI(),6),0,default);
select * from t1;
a b c
-2 2 -0.785398
3.141593 0 1.570796
drop table t1;
set sql_warnings = 0;
# CEIL()
set sql_warnings = 1;
create table t1 (a double, b int as (ceil(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (ceiling(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (1.23,default);
insert ignore into t1 values (-1.23,default);
select * from t1;
a b
1.23 2
-1.23 -1
drop table t1;
set sql_warnings = 0;
# CONV()
set sql_warnings = 1;
create table t1 (a varchar(10), b int, c int, d varchar(10) as (conv(a,b,c)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` varchar(10) GENERATED ALWAYS AS (conv(`a`,`b`,`c`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('a',16,2,default);
insert ignore into t1 values ('6e',18,8,default);
insert into t1 values (-17,10,-18,default);
insert into t1 values (10+'10'+'10'+0xa,10,10,default);
select * from t1;
a b c d
a 16 2 1010
6e 18 8 172
-17 10 -18 -H
40 10 10 40
drop table t1;
set sql_warnings = 0;
# COS()
set sql_warnings = 1;
create table t1 (a double, b double as (format(cos(a),6)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double GENERATED ALWAYS AS (format(cos(`a`),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (format(PI(),6),default);
select * from t1;
a b
3.141593 -1
drop table t1;
set sql_warnings = 0;
# COT()
set sql_warnings = 1;
create table t1 (a double, b double as (format(cot(a),6)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double GENERATED ALWAYS AS (format(cot(`a`),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (12,default);
insert ignore into t1 values (1,default);
select * from t1;
a b
12 -1.572673
1 0.642093
drop table t1;
set sql_warnings = 0;
# CRC32()
set sql_warnings = 1;
create table t1 (a varchar(10), b long as (crc32(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` mediumtext GENERATED ALWAYS AS (crc32(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('MySQL',default);
insert ignore into t1 values ('mysql',default);
select * from t1;
a b
MySQL 3259397556
mysql 2501908538
drop table t1;
set sql_warnings = 0;
# DEGREES()
set sql_warnings = 1;
create table t1 (a double, b double as (format(degrees(a),6)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double GENERATED ALWAYS AS (format(degrees(`a`),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (format(PI(),6),default);
insert ignore into t1 values (format(PI()/2,6),default);
select * from t1;
a b
3.141593 180.00002
1.570796 89.999981
drop table t1;
set sql_warnings = 0;
# /
set sql_warnings = 1;
create table t1 (a double, b double as (a/2));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double GENERATED ALWAYS AS (`a` / 2) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (2,default);
select * from t1;
a b
2 1
drop table t1;
set sql_warnings = 0;
# EXP()
set sql_warnings = 1;
create table t1 (a double, b double as (format(exp(a),6)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double GENERATED ALWAYS AS (format(exp(`a`),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (2,default);
insert ignore into t1 values (-2,default);
insert into t1 values (0,default);
select * from t1;
a b
2 7.389056
-2 0.135335
0 1
drop table t1;
set sql_warnings = 0;
# FLOOR()
set sql_warnings = 1;
create table t1 (a double, b long as (floor(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` mediumtext GENERATED ALWAYS AS (floor(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (1.23,default);
insert ignore into t1 values (-1.23,default);
select * from t1;
a b
1.23 1
-1.23 -2
drop table t1;
set sql_warnings = 0;
# LN()
set sql_warnings = 1;
create table t1 (a double, b double as (format(ln(a),6)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double GENERATED ALWAYS AS (format(ln(`a`),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (2,default);
insert ignore into t1 values (-2,default);
Warnings:
Warning 3047 Invalid argument error: -2 in function ln.
select * from t1;
a b
2 0.693147
-2 NULL
Warnings:
Warning 3047 Invalid argument error: -2 in function ln.
drop table t1;
set sql_warnings = 0;
# LOG()
set sql_warnings = 1;
create table t1 (a double, b double, c double as (format(log(a,b),6)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double DEFAULT NULL,
  `c` double GENERATED ALWAYS AS (format(log(`a`,`b`),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (2,65536,default);
insert ignore into t1 values (10,100,default);
insert into t1 values (1,100,default);
Warnings:
Warning 3047 Invalid argument error: 1 in function log.
select * from t1;
a b c
2 65536 16
10 100 2
1 100 NULL
Warnings:
Warning 3047 Invalid argument error: 1 in function log.
drop table t1;
set sql_warnings = 0;
set sql_warnings = 1;
create table t1 (a double, b double as (format(log(a),6)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double GENERATED ALWAYS AS (format(log(`a`),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (2,default);
insert ignore into t1 values (-2,default);
Warnings:
Warning 3047 Invalid argument error: -2 in function log.
select * from t1;
a b
2 0.693147
-2 NULL
Warnings:
Warning 3047 Invalid argument error: -2 in function log.
drop table t1;
set sql_warnings = 0;
# LOG2()
set sql_warnings = 1;
create table t1 (a double, b double as (format(log2(a),6)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double GENERATED ALWAYS AS (format(log2(`a`),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (65536,default);
insert ignore into t1 values (-100,default);
Warnings:
Warning 3047 Invalid argument error: -100 in function log2.
select * from t1;
a b
65536 16
-100 NULL
Warnings:
Warning 3047 Invalid argument error: -100 in function log2.
drop table t1;
set sql_warnings = 0;
# LOG10()
set sql_warnings = 1;
create table t1 (a double, b double as (format(log10(a),6)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double GENERATED ALWAYS AS (format(log10(`a`),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (2,default);
insert ignore into t1 values (100,default);
insert into t1 values (-100,default);
Warnings:
Warning 3047 Invalid argument error: -100 in function log10.
select * from t1;
a b
2 0.30103
100 2
-100 NULL
Warnings:
Warning 3047 Invalid argument error: -100 in function log10.
drop table t1;
set sql_warnings = 0;
# -
set sql_warnings = 1;
create table t1 (a double, b double as (a-1));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double GENERATED ALWAYS AS (`a` - 1) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (2,default);
select * from t1;
a b
2 1
drop table t1;
set sql_warnings = 0;
# MOD()
set sql_warnings = 1;
create table t1 (a int, b int as (mod(a,10)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (`a` MOD 10) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (1,default);
insert ignore into t1 values (11,default);
select * from t1;
a b
1 1
11 1
drop table t1;
set sql_warnings = 0;
# %
set sql_warnings = 1;
create table t1 (a int, b int as (a % 10));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (`a` MOD 10) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (1,default);
insert ignore into t1 values (11,default);
select * from t1;
a b
1 1
11 1
drop table t1;
set sql_warnings = 0;
# OCT()
set sql_warnings = 1;
create table t1 (a double, b varchar(10) as (oct(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` varchar(10) GENERATED ALWAYS AS (conv(`a`,10,8)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (12,default);
select * from t1;
a b
12 14
drop table t1;
set sql_warnings = 0;
# PI()
set sql_warnings = 1;
create table t1 (a double, b double as (format(PI()*a*a,6)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double GENERATED ALWAYS AS (format(pi() * `a` * `a`,6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (1,default);
select * from t1;
a b
1 3.141593
drop table t1;
set sql_warnings = 0;
# +
set sql_warnings = 1;
create table t1 (a int, b int as (a+1));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (`a` + 1) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (1,default);
select * from t1;
a b
1 2
drop table t1;
set sql_warnings = 0;
# POW, POWER
set sql_warnings = 1;
create table t1 (a int, b int as (pow(a,2)), c int as (power(a,2)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (pow(`a`,2)) VIRTUAL,
  `c` int(11) GENERATED ALWAYS AS (pow(`a`,2)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (1,default,default);
insert ignore into t1 values (2,default,default);
select * from t1;
a b c
1 1 1
2 4 4
drop table t1;
set sql_warnings = 0;
# RADIANS()
set sql_warnings = 1;
create table t1 (a double, b double as (format(radians(a),6)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double GENERATED ALWAYS AS (format(radians(`a`),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (90,default);
select * from t1;
a b
90 1.570796
drop table t1;
set sql_warnings = 0;
# ROUND()
set sql_warnings = 1;
create table t1 (a double, b int as (round(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (round(`a`,0)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (-1.23,default);
insert ignore into t1 values (-1.58,default);
insert into t1 values (1.58,default);
select * from t1;
a b
-1.23 -1
-1.58 -2
1.58 2
drop table t1;
set sql_warnings = 0;
set sql_warnings = 1;
create table t1 (a double, b double, c int as (round(a,b)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double DEFAULT NULL,
  `c` int(11) GENERATED ALWAYS AS (round(`a`,`b`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (1.298,1,default);
insert ignore into t1 values (1.298,0,default);
insert into t1 values (23.298,-1,default);
select * from t1;
a b c
1.298 1 1
1.298 0 1
23.298 -1 20
drop table t1;
set sql_warnings = 0;
# SIGN()
set sql_warnings = 1;
create table t1 (a double, b int as (sign(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (sign(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (-32,default);
insert ignore into t1 values (0,default);
insert into t1 values (234,default);
select * from t1;
a b
-32 -1
0 0
234 1
drop table t1;
set sql_warnings = 0;
# SIN()
set sql_warnings = 1;
create table t1 (a double, b double as (format(sin(a),6)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double GENERATED ALWAYS AS (format(sin(`a`),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (format(PI()/2,6),default);
select * from t1;
a b
1.570796 1
drop table t1;
set sql_warnings = 0;
# SQRT()
set sql_warnings = 1;
create table t1 (a double, b double as (format(sqrt(a),6)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double GENERATED ALWAYS AS (format(sqrt(`a`),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (4,default);
insert ignore into t1 values (20,default);
insert into t1 values (-16,default);
select * from t1;
a b
4 2
20 4.472136
-16 NULL
drop table t1;
set sql_warnings = 0;
# TAN()
set sql_warnings = 1;
create table t1 (a double, b double as (format(tan(a),6)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double GENERATED ALWAYS AS (format(tan(`a`),6)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (format(PI(),6),default);
insert ignore into t1 values (format(PI()+1,6),default);
select * from t1;
a b
3.141593 0
4.141593 1.557409
drop table t1;
set sql_warnings = 0;
# *
set sql_warnings = 1;
create table t1 (a double, b double as (a*3));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double GENERATED ALWAYS AS (`a` * 3) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (0,default);
insert ignore into t1 values (1,default);
insert into t1 values (2,default);
select * from t1;
a b
0 0
1 3
2 6
drop table t1;
set sql_warnings = 0;
# TRUNCATE()
set sql_warnings = 1;
create table t1 (a double, b double as (truncate(a,4)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double GENERATED ALWAYS AS (truncate(`a`,4)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (1.223,default);
insert ignore into t1 values (1.999,default);
insert into t1 values (1.999,default);
insert into t1 values (122,default);
select * from t1;
a b
1.223 1.223
1.999 1.999
1.999 1.999
122 122
drop table t1;
set sql_warnings = 0;
# Unary -
set sql_warnings = 1;
create table t1 (a double, b double as (-a));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` double GENERATED ALWAYS AS (-`a`) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (1,default);
insert ignore into t1 values (-1,default);
select * from t1;
a b
1 -1
-1 1
drop table t1;
set sql_warnings = 0;
#
# STRING FUNCTIONS
#
# ASCII()
set sql_warnings = 1;
create table t1 (a char(2), b int as (ascii(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` char(2) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (ascii(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2',default);
insert ignore into t1 values (2,default);
insert into t1 values ('dx',default);
select * from t1;
a b
2 50
2 50
dx 100
drop table t1;
set sql_warnings = 0;
# BIN()
set sql_warnings = 1;
create table t1 (a int, b varchar(10) as (bin(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` varchar(10) GENERATED ALWAYS AS (conv(`a`,10,2)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (12,default);
select * from t1;
a b
12 1100
drop table t1;
set sql_warnings = 0;
# BIT_LENGTH()
set sql_warnings = 1;
create table t1 (a varchar(10), b long as (bit_length(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` mediumtext GENERATED ALWAYS AS (bit_length(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('text',default);
select * from t1;
a b
text 32
drop table t1;
set sql_warnings = 0;
# CHAR_LENGTH()
set sql_warnings = 1;
create table t1 (a varchar(10), b long as (char_length(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` mediumtext GENERATED ALWAYS AS (char_length(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('text',default);
select * from t1;
a b
text 4
drop table t1;
set sql_warnings = 0;
# CHAR()
set sql_warnings = 1;
create table t1 (a int, b int, c varbinary(10) as (char(a,b)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` varbinary(10) GENERATED ALWAYS AS (char(`a`,`b`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (77,121,default);
select * from t1;
a b c
77 121 My
drop table t1;
set sql_warnings = 0;
# CHARACTER_LENGTH()
set sql_warnings = 1;
create table t1 (a varchar(10), b long as (character_length(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` mediumtext GENERATED ALWAYS AS (char_length(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('text',default);
select * from t1;
a b
text 4
drop table t1;
set sql_warnings = 0;
# CONCAT_WS()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c varchar(20) as (concat_ws(',',a,b)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` varchar(20) GENERATED ALWAYS AS (concat_ws(',',`a`,`b`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('value1','value2',default);
select * from t1;
a b c
value1 value2 value1,value2
drop table t1;
set sql_warnings = 0;
# CONCAT()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c varchar(20) as (concat(a,',',b)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` varchar(20) GENERATED ALWAYS AS (concat(`a`,',',`b`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('value1','value2',default);
select * from t1;
a b c
value1 value2 value1,value2
drop table t1;
set sql_warnings = 0;
# ELT()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c int, d varchar(10) as (elt(c,a,b)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` varchar(10) GENERATED ALWAYS AS (elt(`c`,`a`,`b`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('value1','value2',1,default);
insert ignore into t1 values ('value1','value2',2,default);
select * from t1;
a b c d
value1 value2 1 value1
value1 value2 2 value2
drop table t1;
set sql_warnings = 0;
# EXPORT_SET()
set sql_warnings = 1;
create table t1 (a int, b varchar(10) as (export_set(a,'1','0','',10)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` varchar(10) GENERATED ALWAYS AS (export_set(`a`,'1','0','',10)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (6,default);
select * from t1;
a b
6 0110000000
drop table t1;
set sql_warnings = 0;
# FIELD()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c int as (field('aa',a,b)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` int(11) GENERATED ALWAYS AS (field('aa',`a`,`b`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('aa','bb',default);
insert ignore into t1 values ('bb','aa',default);
select * from t1;
a b c
aa bb 1
bb aa 2
drop table t1;
set sql_warnings = 0;
# FIND_IN_SET()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c int as (find_in_set(a,b)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` int(11) GENERATED ALWAYS AS (find_in_set(`a`,`b`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('aa','aa,bb,cc',default);
insert ignore into t1 values ('aa','bb,aa,cc',default);
select * from t1;
a b c
aa aa,bb,cc 1
aa bb,aa,cc 2
drop table t1;
set sql_warnings = 0;
# FORMAT()
set sql_warnings = 1;
create table t1 (a double, b varchar(20) as (format(a,2)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` double DEFAULT NULL,
  `b` varchar(20) GENERATED ALWAYS AS (format(`a`,2)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (12332.123456,default);
select * from t1;
a b
12332.123456 12,332.12
drop table t1;
set sql_warnings = 0;
# HEX()
set sql_warnings = 1;
create table t1 (a int, b varchar(10) as (hex(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` varchar(10) GENERATED ALWAYS AS (hex(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (17,default);
select * from t1;
a b
17 11
drop table t1;
set sql_warnings = 0;
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10) as (hex(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) GENERATED ALWAYS AS (hex(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('abc',default);
select * from t1;
a b
abc 616263
drop table t1;
set sql_warnings = 0;
# INSERT()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c varchar(20) as (insert(a,length(a),length(b),b)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` varchar(20) GENERATED ALWAYS AS (insert(`a`,octet_length(`a`),octet_length(`b`),`b`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('start,','end',default);
select * from t1;
a b c
start, end startend
drop table t1;
set sql_warnings = 0;
# INSTR()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c int as (instr(a,b)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` int(11) GENERATED ALWAYS AS (locate(`b`,`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('foobarbar,','bar',default);
insert ignore into t1 values ('xbar,','foobar',default);
select * from t1;
a b c
foobarbar, bar 4
xbar, foobar 0
drop table t1;
set sql_warnings = 0;
# LCASE()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10) as (lcase(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) GENERATED ALWAYS AS (lcase(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('MySQL',default);
select * from t1;
a b
MySQL mysql
drop table t1;
set sql_warnings = 0;
# LEFT()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(5) as (left(a,5)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(5) GENERATED ALWAYS AS (left(`a`,5)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('foobarbar',default);
select * from t1;
a b
foobarbar fooba
drop table t1;
set sql_warnings = 0;
# LENGTH()
set sql_warnings = 1;
create table t1 (a varchar(10), b int as (length(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (octet_length(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('text',default);
select * from t1;
a b
text 4
drop table t1;
set sql_warnings = 0;
# LIKE
set sql_warnings = 1;
create table t1 (a varchar(10), b bool as (a like 'H%!o' escape '!'));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` tinyint(1) GENERATED ALWAYS AS (`a` like 'H%!o' escape '!') VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('Hello',default);
insert ignore into t1 values ('MySQL',default);
select * from t1;
a b
Hello 1
MySQL 0
drop table t1;
set sql_warnings = 0;
# LOCATE()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10) as (locate('bar',a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) GENERATED ALWAYS AS (locate('bar',`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('foobarbar',default);
select * from t1;
a b
foobarbar 4
drop table t1;
set sql_warnings = 0;
# LOWER()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10) as (lower(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) GENERATED ALWAYS AS (lcase(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('MySQL',default);
select * from t1;
a b
MySQL mysql
drop table t1;
set sql_warnings = 0;
# LPAD()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10) as (lpad(a,4,' ')));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) GENERATED ALWAYS AS (lpad(`a`,4,' ')) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('MySQL',default);
insert ignore into t1 values ('M',default);
select * from t1;
a b
MySQL MySQ
M    M
drop table t1;
set sql_warnings = 0;
# LTRIM()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10) as (ltrim(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) GENERATED ALWAYS AS (ltrim(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('  MySQL',default);
insert ignore into t1 values ('MySQL',default);
select * from t1;
a b
  MySQL MySQL
MySQL MySQL
drop table t1;
set sql_warnings = 0;
# MAKE_SET()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c int, d varchar(30) as (make_set(c,a,b)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` varchar(30) GENERATED ALWAYS AS (make_set(`c`,`a`,`b`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('a','b',1,default);
insert ignore into t1 values ('a','b',3,default);
select * from t1;
a b c d
a b 1 a
a b 3 a,b
drop table t1;
set sql_warnings = 0;
# MID()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10) as (mid(a,1,2)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) GENERATED ALWAYS AS (substr(`a`,1,2)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('foobarbar',default);
select * from t1;
a b
foobarbar fo
drop table t1;
set sql_warnings = 0;
# NOT LIKE
set sql_warnings = 1;
create table t1 (a varchar(10), b bool as (a not like 'H%o'));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` tinyint(1) GENERATED ALWAYS AS (`a`  not like 'H%o') VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('Hello',default);
insert ignore into t1 values ('MySQL',default);
select * from t1;
a b
Hello 0
MySQL 1
drop table t1;
set sql_warnings = 0;
# NOT REGEXP
set sql_warnings = 1;
create table t1 (a varchar(10), b bool as (a not regexp 'H.+o'));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` tinyint(1) GENERATED ALWAYS AS (!(`a` regexp 'H.+o')) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('Hello',default);
insert ignore into t1 values ('hello',default);
select * from t1;
a b
Hello 0
hello 0
drop table t1;
set sql_warnings = 0;
# OCTET_LENGTH()
set sql_warnings = 1;
create table t1 (a varchar(10), b int as (octet_length(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (octet_length(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('text',default);
select * from t1;
a b
text 4
drop table t1;
set sql_warnings = 0;
# ORD()
set sql_warnings = 1;
create table t1 (a varchar(10), b long as (ord(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` mediumtext GENERATED ALWAYS AS (ord(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2',default);
select * from t1;
a b
2 50
drop table t1;
set sql_warnings = 0;
# POSITION()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10) as (position('bar' in a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) GENERATED ALWAYS AS (locate('bar',`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('foobarbar',default);
select * from t1;
a b
foobarbar 4
drop table t1;
set sql_warnings = 0;
# QUOTE()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10) as (quote(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) GENERATED ALWAYS AS (quote(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('Don\'t',default);
select * from t1;
a b
Don't 'Don\'t'
drop table t1;
set sql_warnings = 0;
# REGEXP()
set sql_warnings = 1;
create table t1 (a varchar(10), b bool as (a regexp 'H.+o'));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` tinyint(1) GENERATED ALWAYS AS (`a` regexp 'H.+o') VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('Hello',default);
insert ignore into t1 values ('hello',default);
select * from t1;
a b
Hello 1
hello 1
drop table t1;
set sql_warnings = 0;
# REPEAT()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(30) as (repeat(a,3)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(30) GENERATED ALWAYS AS (repeat(`a`,3)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('MySQL',default);
select * from t1;
a b
MySQL MySQLMySQLMySQL
drop table t1;
set sql_warnings = 0;
# REPLACE()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(30) as (replace(a,'aa','bb')));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(30) GENERATED ALWAYS AS (replace(`a`,'aa','bb')) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('maa',default);
select * from t1;
a b
maa mbb
drop table t1;
set sql_warnings = 0;
# REVERSE()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(30) as (reverse(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(30) GENERATED ALWAYS AS (reverse(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('maa',default);
select * from t1;
a b
maa aam
drop table t1;
set sql_warnings = 0;
# RIGHT()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10) as (right(a,4)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) GENERATED ALWAYS AS (right(`a`,4)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('foobarbar',default);
select * from t1;
a b
foobarbar rbar
drop table t1;
set sql_warnings = 0;
# RLIKE()
set sql_warnings = 1;
create table t1 (a varchar(10), b bool as (a rlike 'H.+o'));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` tinyint(1) GENERATED ALWAYS AS (`a` regexp 'H.+o') VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('Hello',default);
insert ignore into t1 values ('MySQL',default);
select * from t1;
a b
Hello 1
MySQL 0
drop table t1;
set sql_warnings = 0;
# RPAD()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10) as (rpad(a,4,'??')));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) GENERATED ALWAYS AS (rpad(`a`,4,'??')) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('He',default);
select * from t1;
a b
He He??
drop table t1;
set sql_warnings = 0;
# RTRIM();
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10) as (rtrim(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) GENERATED ALWAYS AS (rtrim(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('Hello  ',default);
select * from t1;
a b
Hello   Hello
drop table t1;
set sql_warnings = 0;
# SOUNDEX()
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(20) as (soundex(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(20) GENERATED ALWAYS AS (soundex(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('Hello',default);
select * from t1;
a b
Hello H400
drop table t1;
set sql_warnings = 0;
# SOUNDS LIKE
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c bool as (a sounds like b));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` tinyint(1) GENERATED ALWAYS AS (soundex(`a`) = soundex(`b`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('Hello','Hello',default);
insert ignore into t1 values ('Hello','MySQL',default);
insert into t1 values ('Hello','hello',default);
select * from t1;
a b c
Hello Hello 1
Hello MySQL 0
Hello hello 1
drop table t1;
set sql_warnings = 0;
# SPACE()
set sql_warnings = 1;
create table t1 (a varchar(5), b varchar(10) as (concat(a,space(5))));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(5) DEFAULT NULL,
  `b` varchar(10) GENERATED ALWAYS AS (concat(`a`,space(5))) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('Hello', default);
select * from t1;
a b
Hello Hello
drop table t1;
set sql_warnings = 0;
# STRCMP()
set sql_warnings = 1;
create table t1 (a varchar(9), b varchar(9), c tinyint(1) as (strcmp(a,b)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(9) DEFAULT NULL,
  `b` varchar(9) DEFAULT NULL,
  `c` tinyint(1) GENERATED ALWAYS AS (strcmp(`a`,`b`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('Hello','Hello', default);
insert ignore into t1 values ('Hello','Hello1', default);
select * from t1;
a b c
Hello Hello 0
Hello Hello1 -1
drop table t1;
set sql_warnings = 0;
# SUBSTR()
set sql_warnings = 1;
create table t1 (a varchar(5), b varchar(10) as (substr(a,2)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(5) DEFAULT NULL,
  `b` varchar(10) GENERATED ALWAYS AS (substr(`a`,2)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('Hello',default);
select * from t1;
a b
Hello ello
drop table t1;
set sql_warnings = 0;
# SUBSTRING_INDEX()
set sql_warnings = 1;
create table t1 (a varchar(15), b varchar(10) as (substring_index(a,'.',2)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(15) DEFAULT NULL,
  `b` varchar(10) GENERATED ALWAYS AS (substring_index(`a`,'.',2)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('www.mysql.com',default);
select * from t1;
a b
www.mysql.com www.mysql
drop table t1;
set sql_warnings = 0;
# SUBSTRING()
set sql_warnings = 1;
create table t1 (a varchar(5), b varchar(10) as (substring(a from 2 for 2)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(5) DEFAULT NULL,
  `b` varchar(10) GENERATED ALWAYS AS (substr(`a`,2,2)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('Hello',default);
select * from t1;
a b
Hello el
drop table t1;
set sql_warnings = 0;
# TRIM()
set sql_warnings = 1;
create table t1 (a varchar(15), b varchar(10) as (trim(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(15) DEFAULT NULL,
  `b` varchar(10) GENERATED ALWAYS AS (trim(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (' aa ',default);
select * from t1;
a b
aa  aa
drop table t1;
set sql_warnings = 0;
# UCASE()
set sql_warnings = 1;
create table t1 (a varchar(5), b varchar(10) as (ucase(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(5) DEFAULT NULL,
  `b` varchar(10) GENERATED ALWAYS AS (ucase(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('MySQL',default);
select * from t1;
a b
MySQL MYSQL
drop table t1;
set sql_warnings = 0;
# UNHEX()
set sql_warnings = 1;
create table t1 (a varchar(15), b varchar(10) as (unhex(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(15) DEFAULT NULL,
  `b` varchar(10) GENERATED ALWAYS AS (unhex(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('4D7953514C',default);
select * from t1;
a b
4D7953514C MySQL
drop table t1;
set sql_warnings = 0;
# UPPER()
set sql_warnings = 1;
create table t1 (a varchar(5), b varchar(10) as (upper(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(5) DEFAULT NULL,
  `b` varchar(10) GENERATED ALWAYS AS (ucase(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('MySQL',default);
select * from t1;
a b
MySQL MYSQL
drop table t1;
set sql_warnings = 0;
#
# CONTROL FLOW FUNCTIONS
#
# CASE
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(16) as (case a when NULL then 'asd' when 'b' then 'B' else a end));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(16) GENERATED ALWAYS AS (case `a` when NULL then 'asd' when 'b' then 'B' else `a` end) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (NULL,default);
insert ignore into t1 values ('b',default);
insert into t1 values ('c',default);
select * from t1;
a b
NULL NULL
b B
c c
drop table t1;
set sql_warnings = 0;
# IF
set sql_warnings = 1;
create table t1 (a int, b int, c int as (if(a=1,a,b)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) GENERATED ALWAYS AS (if(`a` = 1,`a`,`b`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (1,2,default);
insert ignore into t1 values (3,4,default);
select * from t1;
a b c
1 2 1
3 4 4
drop table t1;
set sql_warnings = 0;
# IFNULL
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c varchar(10) as (ifnull(a,'DEFAULT')));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` varchar(10) GENERATED ALWAYS AS (ifnull(`a`,'DEFAULT')) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (NULL,'adf',default);
insert ignore into t1 values ('a','adf',default);
select * from t1;
a b c
NULL adf DEFAULT
a adf a
drop table t1;
set sql_warnings = 0;
# NULLIF
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10) as (nullif(a,'DEFAULT')));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) GENERATED ALWAYS AS (nullif(`a`,'DEFAULT')) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('DEFAULT',default);
insert ignore into t1 values ('a',default);
select * from t1;
a b
DEFAULT NULL
a a
drop table t1;
set sql_warnings = 0;
#
# OPERATORS
#
# AND, &&
set sql_warnings = 1;
create table t1 (a int, b bool as (a>0 && a<2));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` tinyint(1) GENERATED ALWAYS AS (`a` > 0 and `a` < 2) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (-1,default);
insert ignore into t1 values (1,default);
select * from t1;
a b
-1 0
1 1
drop table t1;
set sql_warnings = 0;
# BETWEEN ... AND ...
set sql_warnings = 1;
create table t1 (a int, b bool as (a between 0 and 2));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` tinyint(1) GENERATED ALWAYS AS (`a` between 0 and 2) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (-1,default);
insert ignore into t1 values (1,default);
select * from t1;
a b
-1 0
1 1
drop table t1;
set sql_warnings = 0;
# BINARY
set sql_warnings = 1;
create table t1 (a varchar(10), b varbinary(10) as (binary a));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varbinary(10) GENERATED ALWAYS AS (cast(`a` as char charset binary)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('11',default);
insert ignore into t1 values (1,default);
select * from t1;
a b
11 11
1 1
drop table t1;
set sql_warnings = 0;
# &
set sql_warnings = 1;
create table t1 (a int, b int as (a & 5));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (`a` & 5) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (1,default);
insert ignore into t1 values (0,default);
select * from t1;
a b
1 1
0 0
drop table t1;
set sql_warnings = 0;
# ~
set sql_warnings = 1;
create table t1 (a int, b int as (~a));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (~`a`) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (1,default);
Warnings:
Warning 1264 Out of range value for column 'b' at row 1
select * from t1;
a b
1 2147483647
drop table t1;
set sql_warnings = 0;
# |
set sql_warnings = 1;
create table t1 (a int, b int as (a | 5));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (`a` | 5) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (1,default);
insert ignore into t1 values (0,default);
insert into t1 values (2,default);
select * from t1;
a b
1 5
0 5
2 7
drop table t1;
set sql_warnings = 0;
# ^
set sql_warnings = 1;
create table t1 (a int, b int as (a ^ 5));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (`a` ^ 5) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (1,default);
insert ignore into t1 values (0,default);
insert into t1 values (2,default);
select * from t1;
a b
1 4
0 5
2 7
drop table t1;
set sql_warnings = 0;
# DIV
set sql_warnings = 1;
create table t1 (a int, b int as (a div 5));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (`a` DIV 5) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (1,default);
insert ignore into t1 values (7,default);
select * from t1;
a b
1 0
7 1
drop table t1;
set sql_warnings = 0;
# <=>
set sql_warnings = 1;
create table t1 (a int, b int, c bool as (a <=> b));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` tinyint(1) GENERATED ALWAYS AS (`a` <=> `b`) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (1,1,default);
insert ignore into t1 values (NULL,NULL,default);
insert into t1 values (1,NULL,default);
select * from t1;
a b c
1 1 1
NULL NULL 1
1 NULL 0
drop table t1;
set sql_warnings = 0;
# =
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c bool as (a=b));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` tinyint(1) GENERATED ALWAYS AS (`a` = `b`) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('a','b',default);
insert ignore into t1 values ('a','a',default);
select * from t1;
a b c
a b 0
a a 1
drop table t1;
set sql_warnings = 0;
# >=
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c bool as (a >= b));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` tinyint(1) GENERATED ALWAYS AS (`a` >= `b`) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('a','b',default);
insert ignore into t1 values ('a','a',default);
select * from t1;
a b c
a b 0
a a 1
drop table t1;
set sql_warnings = 0;
# >
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c bool as (a > b));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` tinyint(1) GENERATED ALWAYS AS (`a` > `b`) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('a','b',default);
insert ignore into t1 values ('a','a',default);
select * from t1;
a b c
a b 0
a a 0
drop table t1;
set sql_warnings = 0;
# IS NOT NULL
set sql_warnings = 1;
create table t1 (a int, b bool as (a is not null));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` tinyint(1) GENERATED ALWAYS AS (`a` is not null) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (1,default);
insert ignore into t1 values (NULL,default);
select * from t1;
a b
1 1
NULL 0
drop table t1;
set sql_warnings = 0;
# IS NULL
set sql_warnings = 1;
create table t1 (a int, b bool as (a is null));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` tinyint(1) GENERATED ALWAYS AS (`a` is null) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (1,default);
insert ignore into t1 values (NULL,default);
select * from t1;
a b
1 0
NULL 1
drop table t1;
set sql_warnings = 0;
# <<
set sql_warnings = 1;
create table t1 (a int, b int as (a << 2));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (`a` << 2) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (1,default);
insert ignore into t1 values (3,default);
select * from t1;
a b
1 4
3 12
drop table t1;
set sql_warnings = 0;
# <=
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c bool as (a <= b));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` tinyint(1) GENERATED ALWAYS AS (`a` <= `b`) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('b','a',default);
insert ignore into t1 values ('b','b',default);
insert into t1 values ('b','c',default);
select * from t1;
a b c
b a 0
b b 1
b c 1
drop table t1;
set sql_warnings = 0;
# <
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c bool as (a < b));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` tinyint(1) GENERATED ALWAYS AS (`a` < `b`) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('b','a',default);
insert ignore into t1 values ('b','b',default);
insert into t1 values ('b','c',default);
select * from t1;
a b c
b a 0
b b 0
b c 1
drop table t1;
set sql_warnings = 0;
# NOT BETWEEN ... AND ...
set sql_warnings = 1;
create table t1 (a int, b bool as (a not between 0 and 2));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` tinyint(1) GENERATED ALWAYS AS (`a` not between 0 and 2) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (-1,default);
insert ignore into t1 values (1,default);
select * from t1;
a b
-1 1
1 0
drop table t1;
set sql_warnings = 0;
# <>
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c bool as (a <> b));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` tinyint(1) GENERATED ALWAYS AS (`a` <> `b`) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('b','a',default);
insert ignore into t1 values ('b','b',default);
insert into t1 values ('b','c',default);
select * from t1;
a b c
b a 1
b b 0
b c 1
drop table t1;
set sql_warnings = 0;
# !=
set sql_warnings = 1;
create table t1 (a varchar(10), b varchar(10), c bool as (a != b));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` tinyint(1) GENERATED ALWAYS AS (`a` <> `b`) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('b','a',default);
insert ignore into t1 values ('b','b',default);
insert into t1 values ('b','c',default);
select * from t1;
a b c
b a 1
b b 0
b c 1
drop table t1;
set sql_warnings = 0;
# ||, OR
set sql_warnings = 1;
create table t1 (a int, b int as (a>5 || a<3));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (`a` > 5 or `a` < 3) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (1,default);
insert ignore into t1 values (4,default);
select * from t1;
a b
1 1
4 0
drop table t1;
set sql_warnings = 0;
# >>
set sql_warnings = 1;
create table t1 (a int, b int as (a >> 2));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (`a` >> 2) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (8,default);
insert ignore into t1 values (3,default);
select * from t1;
a b
8 2
3 0
drop table t1;
set sql_warnings = 0;
# XOR
set sql_warnings = 1;
create table t1 (a int, b int as (a xor 5));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (`a` xor 5) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (0,default);
insert ignore into t1 values (1,default);
insert into t1 values (2,default);
select * from t1;
a b
0 1
1 0
2 0
drop table t1;
set sql_warnings = 0;
#
# DATE AND TIME FUNCTIONS
#
# ADDDATE()
set sql_warnings = 1;
create table t1 (a datetime, b datetime as (adddate(a,interval 1 month)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` datetime GENERATED ALWAYS AS (`a` + interval 1 month) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2008-08-31',default);
select * from t1;
a b
2008-08-31 00:00:00 2008-09-30 00:00:00
drop table t1;
set sql_warnings = 0;
# ADDTIME()
set sql_warnings = 1;
create table t1 (a datetime, b datetime as (addtime(a,'02:00:00')));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` datetime GENERATED ALWAYS AS (addtime(`a`,'02:00:00')) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2008-08-31',default);
select * from t1;
a b
2008-08-31 00:00:00 2008-08-31 02:00:00
drop table t1;
set sql_warnings = 0;
# CONVERT_TZ()
set sql_warnings = 1;
create table t1 (a datetime, b datetime as (convert_tz(a,'MET','UTC')));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` datetime GENERATED ALWAYS AS (convert_tz(`a`,'MET','UTC')) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2008-08-31',default);
select * from t1;
a b
2008-08-31 00:00:00 2008-08-30 22:00:00
drop table t1;
set sql_warnings = 0;
# DATE_ADD()
set sql_warnings = 1;
create table t1 (a datetime, b datetime as (date_add(a,interval 1 month)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` datetime GENERATED ALWAYS AS (`a` + interval 1 month) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2008-08-31',default);
select * from t1;
a b
2008-08-31 00:00:00 2008-09-30 00:00:00
drop table t1;
set sql_warnings = 0;
# DATE_SUB()
set sql_warnings = 1;
create table t1 (a datetime, b datetime as (date_sub(a,interval 1 month)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` datetime GENERATED ALWAYS AS (`a` - interval 1 month) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2008-08-31',default);
select * from t1;
a b
2008-08-31 00:00:00 2008-07-31 00:00:00
drop table t1;
set sql_warnings = 0;
# DATE()
set sql_warnings = 1;
create table t1 (a datetime, b datetime as (date(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` datetime GENERATED ALWAYS AS (cast(`a` as date)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2008-08-31 02:00:00',default);
select * from t1;
a b
2008-08-31 02:00:00 2008-08-31 00:00:00
drop table t1;
set sql_warnings = 0;
# DATEDIFF()
set sql_warnings = 1;
create table t1 (a datetime, b long as (datediff(a,'2000-01-01')));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` mediumtext GENERATED ALWAYS AS (to_days(`a`) - to_days('2000-01-01')) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2008-08-31',default);
select * from t1;
a b
2008-08-31 00:00:00 3165
drop table t1;
set sql_warnings = 0;
# DAY()
set sql_warnings = 1;
create table t1 (a datetime, b int as (day(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (dayofmonth(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2008-08-31',default);
select * from t1;
a b
2008-08-31 00:00:00 31
drop table t1;
set sql_warnings = 0;
# DAYOFMONTH()
set sql_warnings = 1;
create table t1 (a datetime, b int as (dayofmonth(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (dayofmonth(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2008-08-31',default);
select * from t1;
a b
2008-08-31 00:00:00 31
drop table t1;
set sql_warnings = 0;
# DAYOFWEEK()
set sql_warnings = 1;
create table t1 (a datetime, b int as (dayofweek(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (dayofweek(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2008-08-31',default);
select * from t1;
a b
2008-08-31 00:00:00 1
drop table t1;
set sql_warnings = 0;
# DAYOFYEAR()
set sql_warnings = 1;
create table t1 (a datetime, b int as (dayofyear(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (dayofyear(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2008-08-31',default);
select * from t1;
a b
2008-08-31 00:00:00 244
drop table t1;
set sql_warnings = 0;
# EXTRACT
set sql_warnings = 1;
create table t1 (a datetime, b int as (extract(year from a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (extract(year from `a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2008-08-31',default);
select * from t1;
a b
2008-08-31 00:00:00 2008
drop table t1;
set sql_warnings = 0;
# FROM_DAYS()
set sql_warnings = 1;
create table t1 (a long, b datetime as (from_days(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` mediumtext DEFAULT NULL,
  `b` datetime GENERATED ALWAYS AS (from_days(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (730669,default);
select * from t1;
a b
730669 2000-07-03 00:00:00
drop table t1;
set sql_warnings = 0;
# FROM_UNIXTIME()
set time_zone='UTC';
set sql_warnings = 1;
create table t1 (a long, b datetime as (from_unixtime(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` mediumtext DEFAULT NULL,
  `b` datetime GENERATED ALWAYS AS (from_unixtime(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (1196440219,default);
select * from t1;
a b
1196440219 2007-11-30 16:30:19
drop table t1;
set sql_warnings = 0;
# HOUR()
set sql_warnings = 1;
create table t1 (a time, b long as (hour(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` time DEFAULT NULL,
  `b` mediumtext GENERATED ALWAYS AS (hour(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('10:05:03',default);
select * from t1;
a b
10:05:03 10
drop table t1;
set sql_warnings = 0;
# LAST_DAY()
set sql_warnings = 1;
create table t1 (a datetime, b datetime as (last_day(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` datetime GENERATED ALWAYS AS (last_day(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2003-02-05',default);
insert ignore into t1 values ('2003-02-32',default);
Warnings:
Warning 1265 Data truncated for column 'a' at row 1
select * from t1;
a b
2003-02-05 00:00:00 2003-02-28 00:00:00
0000-00-00 00:00:00 NULL
drop table t1;
set sql_warnings = 0;
# MAKEDATE()
set sql_warnings = 1;
create table t1 (a int, b datetime as (makedate(a,1)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` datetime GENERATED ALWAYS AS (makedate(`a`,1)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (2001,default);
select * from t1;
a b
2001 2001-01-01 00:00:00
drop table t1;
set sql_warnings = 0;
# MAKETIME()
set sql_warnings = 1;
create table t1 (a int, b time as (maketime(a,1,3)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` time GENERATED ALWAYS AS (maketime(`a`,1,3)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (12,default);
select * from t1;
a b
12 12:01:03
drop table t1;
set sql_warnings = 0;
# MICROSECOND()
set sql_warnings = 1;
create table t1 (a datetime, b long as (microsecond(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` mediumtext GENERATED ALWAYS AS (microsecond(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2009-12-31 12:00:00.123456',default);
insert ignore into t1 values ('2009-12-31 23:59:59.000010',default);
select * from t1;
a b
2009-12-31 12:00:00 0
2009-12-31 23:59:59 0
drop table t1;
set sql_warnings = 0;
# MINUTE()
set sql_warnings = 1;
create table t1 (a datetime, b int as (minute(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (minute(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2009-12-31 23:59:59.000010',default);
select * from t1;
a b
2009-12-31 23:59:59 59
drop table t1;
set sql_warnings = 0;
# MONTH()
set sql_warnings = 1;
create table t1 (a datetime, b int as (month(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (month(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2009-12-31 23:59:59.000010',default);
select * from t1;
a b
2009-12-31 23:59:59 12
drop table t1;
set sql_warnings = 0;
# PERIOD_ADD()
set sql_warnings = 1;
create table t1 (a int, b int as (period_add(a,2)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (period_add(`a`,2)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (200801,default);
select * from t1;
a b
200801 200803
drop table t1;
set sql_warnings = 0;
# PERIOD_DIFF()
set sql_warnings = 1;
create table t1 (a int, b int, c int as (period_diff(a,b)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) GENERATED ALWAYS AS (period_diff(`a`,`b`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (200802,200703,default);
select * from t1;
a b c
200802 200703 11
drop table t1;
set sql_warnings = 0;
# QUARTER()
set sql_warnings = 1;
create table t1 (a datetime, b int as (quarter(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (quarter(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2008-08-31',default);
select * from t1;
a b
2008-08-31 00:00:00 3
drop table t1;
set sql_warnings = 0;
# SEC_TO_TIME()
set sql_warnings = 1;
create table t1 (a long, b time as (sec_to_time(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` mediumtext DEFAULT NULL,
  `b` time GENERATED ALWAYS AS (sec_to_time(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (2378,default);
select * from t1;
a b
2378 00:39:38
drop table t1;
set sql_warnings = 0;
# SECOND()
set sql_warnings = 1;
create table t1 (a datetime, b int as (second(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (second(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('10:05:03',default);
select * from t1;
a b
2010-05-03 00:00:00 0
drop table t1;
set sql_warnings = 0;
# STR_TO_DATE()
set sql_warnings = 1;
create table t1 (a varchar(64), b datetime as (str_to_date(a,'%m/%d/%Y')));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(64) DEFAULT NULL,
  `b` datetime GENERATED ALWAYS AS (str_to_date(`a`,'%m/%d/%Y')) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('04/30/2004',default);
select * from t1;
a b
04/30/2004 2004-04-30 00:00:00
drop table t1;
set sql_warnings = 0;
# STR_TO_DATE() STORED
set sql_warnings = 1;
create table t1 (a varchar(64), b datetime as (str_to_date(a,'%d %M %Y','en_US')) stored);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(64) DEFAULT NULL,
  `b` datetime GENERATED ALWAYS AS (str_to_date(`a`,'%d %M %Y','en_US')) STORED
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('04 April 2004',default);
select * from t1;
a b
04 April 2004 2004-04-04 00:00:00
drop table t1;
set sql_warnings = 0;
# SUBDATE()
set sql_warnings = 1;
create table t1 (a datetime, b datetime as (subdate(a,interval 1 month)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` datetime GENERATED ALWAYS AS (`a` - interval 1 month) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2008-08-31',default);
select * from t1;
a b
2008-08-31 00:00:00 2008-07-31 00:00:00
drop table t1;
set sql_warnings = 0;
# SUBTIME()
set sql_warnings = 1;
create table t1 (a datetime, b datetime as (subtime(a,'02:00:00')));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` datetime GENERATED ALWAYS AS (subtime(`a`,'02:00:00')) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2008-08-31',default);
select * from t1;
a b
2008-08-31 00:00:00 2008-08-30 22:00:00
drop table t1;
set sql_warnings = 0;
# TIME_TO_SEC()
set sql_warnings = 1;
create table t1 (a time, b long as (time_to_sec(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` time DEFAULT NULL,
  `b` mediumtext GENERATED ALWAYS AS (time_to_sec(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('22:23:00',default);
select * from t1;
a b
22:23:00 80580
drop table t1;
set sql_warnings = 0;
# TIME()
set sql_warnings = 1;
create table t1 (a datetime, b time as (time(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` time GENERATED ALWAYS AS (cast(`a` as time)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2008-08-31 02:03:04',default);
select * from t1;
a b
2008-08-31 02:03:04 02:03:04
drop table t1;
set sql_warnings = 0;
# TIMEDIFF()
set sql_warnings = 1;
create table t1 (a datetime, b datetime, c long as (timediff(a,b)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` datetime DEFAULT NULL,
  `c` mediumtext GENERATED ALWAYS AS (timediff(`a`,`b`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2008-12-31 23:59:59.000001','2008-12-30 01:01:01.000002',default);
select * from t1;
a b c
2008-12-31 23:59:59 2008-12-30 01:01:01 46:58:58
drop table t1;
set sql_warnings = 0;
# TIMESTAMP()
set sql_warnings = 1;
create table t1 (a datetime, b timestamp as (timestamp(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` timestamp GENERATED ALWAYS AS (cast(`a` as datetime)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2008-12-31',default);
select * from t1;
a b
2008-12-31 00:00:00 2008-12-31 00:00:00
drop table t1;
set sql_warnings = 0;
# TIMESTAMPADD()
set sql_warnings = 1;
create table t1 (a datetime, b timestamp as (timestampadd(minute,1,a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` timestamp GENERATED ALWAYS AS (`a` + interval 1 minute) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2003-01-02',default);
select * from t1;
a b
2003-01-02 00:00:00 2003-01-02 00:01:00
drop table t1;
set sql_warnings = 0;
# TIMESTAMPDIFF()
set sql_warnings = 1;
create table t1 (a timestamp, b timestamp, c long as (timestampdiff(MONTH, a,b)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` timestamp NULL DEFAULT NULL,
  `b` timestamp NULL DEFAULT NULL,
  `c` mediumtext GENERATED ALWAYS AS (timestampdiff(MONTH,`a`,`b`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2003-02-01','2003-05-01',default);
select * from t1;
a b c
2003-02-01 00:00:00 2003-05-01 00:00:00 3
drop table t1;
set sql_warnings = 0;
# TO_DAYS()
set sql_warnings = 1;
create table t1 (a datetime, b long as (to_days(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` mediumtext GENERATED ALWAYS AS (to_days(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2007-10-07',default);
select * from t1;
a b
2007-10-07 00:00:00 733321
drop table t1;
set sql_warnings = 0;
# WEEK()
set sql_warnings = 1;
create table t1 (a datetime, b int as (week(a,0)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (week(`a`,0)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2008-09-01',default);
select * from t1;
a b
2008-09-01 00:00:00 35
drop table t1;
set sql_warnings = 0;
# WEEKDAY()
set sql_warnings = 1;
create table t1 (a datetime, b int as (weekday(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (weekday(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2008-09-01',default);
select * from t1;
a b
2008-09-01 00:00:00 0
drop table t1;
set sql_warnings = 0;
# WEEKOFYEAR()
set sql_warnings = 1;
create table t1 (a datetime, b int as (weekofyear(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (week(`a`,3)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2008-09-01',default);
select * from t1;
a b
2008-09-01 00:00:00 36
drop table t1;
set sql_warnings = 0;
# YEAR()
set sql_warnings = 1;
create table t1 (a datetime, b int as (year(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (year(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2008-09-01',default);
select * from t1;
a b
2008-09-01 00:00:00 2008
drop table t1;
set sql_warnings = 0;
# YEARWEEK()
set sql_warnings = 1;
create table t1 (a datetime, b int as (yearweek(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (yearweek(`a`,0)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2008-09-01',default);
select * from t1;
a b
2008-09-01 00:00:00 200835
drop table t1;
set sql_warnings = 0;
#
# FULL TEXT SEARCH FUNCTIONS
#
# None.
#
# CAST FUNCTIONS AND OPERATORS
#
# CAST()
set sql_warnings = 1;
create table t1 (a int, b long as (cast(a as unsigned)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` mediumtext GENERATED ALWAYS AS (cast(`a` as unsigned)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (1,default);
insert ignore into t1 values (-1,default);
Warnings:
Note 1105 Cast to unsigned converted negative integer to it's positive complement
select * from t1;
a b
1 1
-1 18446744073709551615
Warnings:
Note 1105 Cast to unsigned converted negative integer to it's positive complement
drop table t1;
set sql_warnings = 0;
# Convert()
set sql_warnings = 1;
create table t1 (a int, b long as (convert(a,unsigned)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` mediumtext GENERATED ALWAYS AS (cast(`a` as unsigned)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (1,default);
insert ignore into t1 values (-1,default);
Warnings:
Note 1105 Cast to unsigned converted negative integer to it's positive complement
select * from t1;
a b
1 1
-1 18446744073709551615
Warnings:
Note 1105 Cast to unsigned converted negative integer to it's positive complement
drop table t1;
set sql_warnings = 0;
#
# XML FUNCTIONS
#
# None.
#
# OTHER FUNCTIONS
#
# AES_DECRYPT(), AES_ENCRYPT()
set sql_warnings = 1;
create table t1 (a varchar(1024), b varchar(1024) as (aes_encrypt(aes_decrypt(a,'adf'),'adf')));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(1024) DEFAULT NULL,
  `b` varchar(1024) GENERATED ALWAYS AS (aes_encrypt(aes_decrypt(`a`,'adf'),'adf')) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('MySQL',default);
select * from t1;
a b
MySQL NULL
drop table t1;
set sql_warnings = 0;
# BIT_COUNT()
set sql_warnings = 1;
create table t1 (a int, b int as (bit_count(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (bit_count(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values (5,default);
select * from t1;
a b
5 2
drop table t1;
set sql_warnings = 0;
# COMPRESS(), UNCOMPRESS()
set sql_warnings = 1;
create table t1 (a varchar(1024), b varchar(1024) as (uncompress(compress(a))));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(1024) DEFAULT NULL,
  `b` varchar(1024) GENERATED ALWAYS AS (uncompress(compress(`a`))) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('MySQL',default);
select * from t1;
a b
MySQL MySQL
drop table t1;
set sql_warnings = 0;
# ENCODE(), DECODE()
set sql_warnings = 1;
create table t1 (a varchar(1024), b varchar(1024) as (decode(encode(a,'abc'),'abc')));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(1024) DEFAULT NULL,
  `b` varchar(1024) GENERATED ALWAYS AS (decode(encode(`a`,'abc'),'abc')) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('MySQL',default);
select * from t1;
a b
MySQL MySQL
drop table t1;
set sql_warnings = 0;
# DEFAULT()
set sql_warnings = 1;
create table t1 (a varchar(1024) default 'aaa', b varchar(1024) as (ifnull(a,default(a))));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(1024) DEFAULT 'aaa',
  `b` varchar(1024) GENERATED ALWAYS AS (ifnull(`a`,default(`a`))) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('any value',default);
select * from t1;
a b
any value any value
drop table t1;
set sql_warnings = 0;
# INET_ATON(), INET_NTOA()
set sql_warnings = 1;
create table t1 (a varchar(1024), b varchar(1024) as (inet_ntoa(inet_aton(a))));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(1024) DEFAULT NULL,
  `b` varchar(1024) GENERATED ALWAYS AS (inet_ntoa(inet_aton(`a`))) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('127.0.0.1',default);
select * from t1;
a b
127.0.0.1 127.0.0.1
drop table t1;
set sql_warnings = 0;
# MD5()
set sql_warnings = 1;
create table t1 (a varchar(1024), b varbinary(32) as (md5(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(1024) DEFAULT NULL,
  `b` varbinary(32) GENERATED ALWAYS AS (md5(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('testing',default);
select * from t1;
a b
testing ae2b1fca515949e5d54fb22b8ed95575
drop table t1;
set sql_warnings = 0;
# OLD_PASSWORD()
set sql_warnings = 1;
create table t1 (a varchar(1024), b varchar(1024) as (old_password(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(1024) DEFAULT NULL,
  `b` varchar(1024) GENERATED ALWAYS AS (old_password(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('badpwd',default);
select * from t1;
a b
badpwd 7f84554057dd964b
drop table t1;
set sql_warnings = 0;
# PASSWORD()
set sql_warnings = 1;
create table t1 (a varchar(1024), b varchar(1024) as (password(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(1024) DEFAULT NULL,
  `b` varchar(1024) GENERATED ALWAYS AS (password(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('badpwd',default);
select * from t1;
a b
badpwd *AAB3E285149C0135D51A520E1940DD3263DC008C
drop table t1;
set sql_warnings = 0;
# SHA1()
set sql_warnings = 1;
create table t1 (a varchar(1024), b varchar(1024) as (sha1(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(1024) DEFAULT NULL,
  `b` varchar(1024) GENERATED ALWAYS AS (sha(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('abc',default);
select * from t1;
a b
abc a9993e364706816aba3e25717850c26c9cd0d89d
drop table t1;
set sql_warnings = 0;
# SHA()
set sql_warnings = 1;
create table t1 (a varchar(1024), b varchar(1024) as (sha(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` varchar(1024) DEFAULT NULL,
  `b` varchar(1024) GENERATED ALWAYS AS (sha(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('abc',default);
select * from t1;
a b
abc a9993e364706816aba3e25717850c26c9cd0d89d
drop table t1;
set sql_warnings = 0;
# UNCOMPRESSED_LENGTH()
set sql_warnings = 1;
create table t1 (a char, b varchar(1024) as (uncompressed_length(compress(repeat(a,30)))));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` char(1) DEFAULT NULL,
  `b` varchar(1024) GENERATED ALWAYS AS (uncompressed_length(compress(repeat(`a`,30)))) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('a',default);
select * from t1;
a b
a 30
drop table t1;
set sql_warnings = 0;
# MONTHNAME()
set sql_warnings = 1;
create table t1 (a date, b varchar(100) as (monthname(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` date DEFAULT NULL,
  `b` varchar(100) GENERATED ALWAYS AS (monthname(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2010-10-10',default);
select * from t1;
a b
2010-10-10 October
drop table t1;
set sql_warnings = 0;
# DAYNAME()
set sql_warnings = 1;
create table t1 (a date, b varchar(100) as (dayname(a)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` date DEFAULT NULL,
  `b` varchar(100) GENERATED ALWAYS AS (dayname(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2011-11-11',default);
select * from t1;
a b
2011-11-11 Friday
drop table t1;
set sql_warnings = 0;
# DATE_FORMAT()
set sql_warnings = 1;
create table t1 (a date, b varchar(100) as (date_format(a, '%W %a %M %b')));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` date DEFAULT NULL,
  `b` varchar(100) GENERATED ALWAYS AS (date_format(`a`,'%W %a %M %b')) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2012-12-12',default);
select * from t1;
a b
2012-12-12 Wednesday Wed December Dec
drop table t1;
set sql_warnings = 0;
# DATE_FORMAT() STORED
set sql_warnings = 1;
create table t1 (a date, b varchar(100) as (date_format(a, '%W %a %M %b', 'de_DE')) STORED);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` date DEFAULT NULL,
  `b` varchar(100) GENERATED ALWAYS AS (date_format(`a`,'%W %a %M %b','de_DE')) STORED
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2012-12-12',default);
select * from t1;
a b
2012-12-12 Mittwoch Mi Dezember Dez
drop table t1;
set sql_warnings = 0;
# CURRENT_USER()
set sql_warnings = 1;
create table t1 (a char, b varchar(32) as (current_user()));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` char(1) DEFAULT NULL,
  `b` varchar(32) GENERATED ALWAYS AS (current_user()) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('a', default);
select * from t1;
a b
a root@localhost
drop table t1;
set sql_warnings = 0;
# TIME_FORMAT()
set sql_warnings = 1;
create table t1 (a datetime, b varchar(10) as (time_format(a,"%H.%i.%S")));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` varchar(10) GENERATED ALWAYS AS (time_format(`a`,'%H.%i.%S')) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2001-01-01 02:03:04',default);
select * from t1;
a b
2001-01-01 02:03:04 02.03.04
drop table t1;
set sql_warnings = 0;
# TIME_FORMAT() STORED
set sql_warnings = 1;
create table t1 (a datetime, b varchar(10) as (time_format(a,"%H.%i.%S")) STORED);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `a` datetime DEFAULT NULL,
  `b` varchar(10) GENERATED ALWAYS AS (time_format(`a`,'%H.%i.%S')) STORED
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values ('2001-01-01 02:03:04',default);
select * from t1;
a b
2001-01-01 02:03:04 02.03.04
drop table t1;
set sql_warnings = 0;

Messung V0.5 in Prozent

¤ Diese beiden folgenden Angebotsgruppen bietet das Unternehmen0.47Angebot ¤

*Eine klare Vorstellung vom Zielzustand

Wurzel

Suchen

PVS Prover

Isabelle Prover

NIST Cobol Testsuite

Cephes Mathematical Library

Vienna Development Method

Haftungshinweis

Die Informationen auf dieser Webseite wurden nach bestem Wissen sorgfältig zusammengestellt. Es wird jedoch weder Vollständigkeit, noch Richtigkeit, noch Qualität der bereit gestellten Informationen zugesichert.