Many developers don't know that MySQL now has support for invisible columns.
But what exactly is an invisible column?
When it arrives?
This attribute is available on MySQL since v8.0.23. According to the documentation:
An invisible column is normally hidden to queries, but can be accessed if explicitly referenced. Prior to MySQL 8.0.23, all columns are visible.
The syntax to create an invisible column is very simple:
create table employees (
id int not null primary key auto_increment,
name varchar(45) not null,
ssn char(11) not null INVISIBLE
) ENGINE = InnoDB;
Certainly, we can use DDL to switch a column between visible and invisible.
alter table employees alter column ssn set visible;
Querying Data
Now, what happens if I execute select * from employees
?
Wow, where is my ssn
column? Well, it's hidden ๐! To retrieve that column, we need to explicitly specify it in our query:
This is a good approach to exclude columns that don't need to be displayed in our query, such as those containing sensitive data.
Generating an invisible column with a Laravel Migration
If you wish to utilize this feature in a Laravel application, you can define your column as follows:
public function up(): void
{
Schema::create('employees', function (Blueprint $table) {
$table->id();
$table->string('name', 45);
$table->char('ssn', 11)->invisible();
});
}
Generating an invisible column with Doctrine
I've defined an entity called Employee
with the following configuration
#[ORM\Entity]
#[ORM\Table(name: 'employees')]
class Employee
{
#[ORM\Id]
#[ORM\Column(type: 'integer')]
#[ORM\GeneratedValue]
private int | null $id;
#[ORM\Column(type: 'string', length: 45)]
private string $name;
#[ORM\Column(columnDefinition: 'char(11) INVISIBLE')]
private string $ssn;
// The other methods of this entity
}
Now, we have an additional security layer to prevent the exposure of our sensitive data!
That's all folks! See you on the next post ๐ ๐๐ป
Top comments (3)
I believe this is also a good case for columns that use json or blob and you want to avoid from select all queries.
if I want to query it from Laravel, how can I do it?
Hello, my friend. In this case, you should specify the field in the query. For example:
๐๐๐ป