How to create this rule in mySQL

Question

How to create this rule in mySQL

Navigation

#1 by (1 votes)
#2 by (0 votes)

5

I have a field in my table:

tipoEndereco char(1);

However this field only receives two possible values, which are:

  1-> i (Instalação) 
  2-> c (Cobrança)

Because of this, I'm thinking, and I would like your opinion, to change it to:

tipo enum("i", "c");

The change is for performance effect. What do you think?

The other guideline, and most importantly, is that I would like to create a rule in MySQL that allows insertion of empty values in the fields, if the value that arrives via query is "c" (Collection). >

Is this possible?

Here's the table:

CREATE TABLE enderecos (
  idEnderecos int(1) unsigned NOT NULL AUTO_INCREMENT,
  idClientes int(10) NOT NULL,
  tipoEndereco char(1) NOT NULL,
  endereco varchar(100) NOT NULL,
  numero varchar(5) NOT NULL DEFAULT '',
  complemento varchar(50) NOT NULL,
  bairro varchar(100) NOT NULL,
  cidade varchar(150) NOT NULL,
  estado char(2) NOT NULL,
  cep char(8) NOT NULL,
  PRIMARY KEY (idEnderecos)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

The idea here is that as soon as the client is written to the Clientes table, get its idClientes generated ( insert_id ) and save the address data in the endereços table.

The form has two addresses, one for installation and one for billing. In the database, the address table is NOT NULL . But if the address is of type "c" (collection), I need the table to accept null values.

How?

mysql sql

asked by anonymous 17.09.2015 / 15:53

2 answers

dynamic url url Enable HOVER without clicking the same div

score 1 · Answer 1

First Question. Yes, there may be a mere performance improvement, but if the number of concurrent transactions is small, it will not make that much difference. The main advantage of using an enum is that you restrict the amount of entries of a user, although code could also do that. But if you need to change or add any type or reuse the code, as in subselects, for example, you will have more problems than advantages, as described in link

2nd Yes, it is possible. If you use both procedures and scripts a CASE clause can be used for this insertion.

insert into tabela2(end_inst,end_cobr) values(select case when tipo_endereco = 'i' then Endereco else null end, case when tipo_endereco = 'c' then Endereco else null end from tabela1)

But I recommend that you maybe review the normalization process of this database, because from the little that I realized there may be redundant data, causing waste and loss of reliability in the database

score 0 · Answer 2

I think it's a good idea to use ENUM here, but it would have been a good idea also if you TINYINT by simulating a BOOLEAN .

But suppose you choose ENUM . So you need to understand the details a bit. ENUM types are numerically indexed, which means that if you have this: type ENUM ('c', 'i'); Then internally c = 0 and i = 1. Also, if you want something like a field with default value and different from NULL , you can create an ENUM field and put the value that you want to always be default (if you do not provide one) at index 0 , that is, the first ENUM value. That way, any entry other than NULL that is not a valid value will automatically be the indexed value in the first position of the ENUM field. Here's what the MySQL documentation says:

The value may also be the empty string ('') or NULL under certain   circumstances:



If you insert an invalid value into an ENUM (that is, a string not   present in the list of permitted values), the empty string is   inserted instead of a special error value. This string can be   distinguished from a "normal"   string has the numeric value 0. More about this later.

If strict SQL mode is enabled, attempts to insert invalid ENUM   values result in an error.


If an ENUM column is declared to allow NULL, the NULL value is a   legal value for the column, and the default value is NULL. If an   ENUM column is declared NOT NULL, its default value is the first   element of the list of permitted values.

Then:

mysql> 
mysql> create table x (
    id int primary key auto_increment, 
    tipo enum('c', 'i') not null);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into x(tipo) values ('i');
Query OK, 1 row affected (0.04 sec)

mysql> insert into x(tipo) values (null);
ERROR 1048 (23000): Column 'tipo' cannot be null
mysql> insert into x(tipo) values ('');
Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> insert into x(tipo) values ('nao existe');
Query OK, 1 row affected, 1 warning (0.09 sec)

mysql> insert into x(tipo) values ('i');
Query OK, 1 row affected (0.03 sec)

mysql> select * from x;
+----+------+
| id | tipo |
+----+------+
|  1 | i    |
|  2 |      |
|  3 |      |
|  4 | i    |
+----+------+
4 rows in set (0.00 sec)

mysql> select * from x where tipo = 0;
+----+------+
| id | tipo |
+----+------+
|  2 |      |
|  3 |      |
+----+------+
2 rows in set (0.00 sec)

mysql>