Create table with some column's type defined as some function's returning type

user_10242416 0
Viewed 71

The following SQL statement will create a table t, with a column c1, whose type is defined as bigint(21) unsigned, which is the returning type of function INET_ATON:

CREATE TABLE t AS SELECT INET_ATON('0.0.0.0') AS c1;

But the statement also inserts a row. I wonder if there is a way to create the table and make its columns' type defined as some function's returning type, without inserting a row.

I tried the following:

CREATE TABLE t LIKE SELECT INET_ATON('0.0.0.0') AS c1;

But this doesn't match the SQL syntax.

Any ideas on how to achieve this? Thanks!

PS: Any standard SQL statement is OK, the MariaDB or MySQL supported statement is also OK.

mariadb mysql sql
1 Answers

use where clause to create a condition which will never be true like below:

CREATE TABLE t AS SELECT INET_ATON('0.0.0.0') AS c1 where 1=2;

This will create the table without adding any rows into it.

user_86516010
Related