How to create invisible columns in MariaDB?

Question

How to create invisible columns in MariaDB?

Navigation

#1 by (1 votes)

1

Inside the database manager mariaDB, we can create a table, indicate the type of data we expect to receive, as well as its longitud and whether it should be NULL .

However, in managers such as Oracle we can define in the version 12c , through the following syntax we can define columns invisibles

CREATE TABLE data(
  id NUMBER,
  name NUMBER,
  DNI NUMBER INVISBLE);

The characteristics offered by the declaration of columns like this are:

Not visible in a SELECT *

They will only appear when the column is named in the syntax of SELECT

The question is how to achieve the same in mariaDB Server ?

sql mariadb

asked by element 18.09.2018 в 03:55

source

1 answer

I do not run a program with Java fixes correctly Decimals when editing record with modal

score 1 · Accepted Answer

Within the MariaDB database manager, since version 10.3.3 we can have this feature available.

Through the following example I show how to declare a column of type invisible

This is the declaration of a table with regular columns

MariaDB [blog]> CREATE TABLE demo(
    -> id BIGINT PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(22) UNIQUE NOT NULL,
    -> features JSON NOT NULL)ENGINE=InnoDB;

Now just after the column name I want to add a column called token of type VARCHAR that will be invisible

MariaDB [blog]> ALTER TABLE demo ADD COLUMN token VARCHAR(100) INVISIBLE NOT NULL 
DEFAULT 'No asignado' AFTER name;

Important note, a column of invisible type in mariaDB must be declared with a value of deafult , otherwise it will return an error indicating what I have already mentioned

Now if we review with the DESCRIBE sentence the demo table, we will see the following

MariaDB [blog]> DESCRIBE demo;
+----------+--------------+------+-----+-------------+----------------+
| Field    | Type         | Null | Key | Default     | Extra          |
+----------+--------------+------+-----+-------------+----------------+
| id       | bigint(20)   | NO   | PRI | NULL        | auto_increment |
| name     | varchar(22)  | NO   | UNI | NULL        |                |
| token    | varchar(100) | NO   |     | No asignado | INVISIBLE      |
| features | longtext     | NO   |     | NULL        |                |
+----------+--------------+------+-----+-------------+----------------+

Where as we can see in the column extra tells us that token also has an extra feature or attribute called invisible

Now let's register a record

MariaDB [blog]> INSERT INTO demo(name, features)
    -> VALUES
    -> ('alfa', '{"cool": "true"}');

Let's make a SELECT * to the table demo to see the information it contains

MariaDB [blog]> SELECT * FROM demo;
+----+------+------------------+
| id | name | features         |
+----+------+------------------+
|  1 | alfa | {"cool": "true"} |
+----+------+------------------+

As we observed the column token keeps its condition invisible, as long as we do not explicitly declare it in SELECT , in the following way in which case it will be visible

MariaDB [blog]> SELECT id, name, token, features FROM demo;
+----+------+-------------+------------------+
| id | name | token       | features         |
+----+------+-------------+------------------+
|  1 | alfa | No asignado | {"cool": "true"} |
+----+------+-------------+------------------+

As you can see in the previous example, now it is not only vivisble the column token if not that in addition to not declaring a value, took the what we gave him for DEFAULT