Ha alguns meses realizei um treinamento de MySQL ministrado pela HTI, que acredito seja a única parceira oficial da Oracle Sun para treinamentos em MySQL (pelo menos era antes da aquisição). O treinamento foi muito bom, por sinal recomendo, teve como instrutor o Alexandre Almeida atual colaborador do MariaDB e mantenedor do MySQL Labs. Durante o treinamento tivemos vários assuntos interessantes abordados mas um ponto em particular me chamou bastante a atenção principalmente pela curiosidade, tratava-se de uma configuração regida por um parâmetro chamado SQL Mode.
Antes de explicar sobre a utilização da configuração em questão vou aproveitar a oportunidade para salientar uma das características do MySQL e talvez de muitos aplicativos open source, a premissa da simplicidade. Uma das primeiras coisas que aprendi sobre o MySQL e uma de tantas que um amigo do mundo open source, Marcelo Leal, me ensinou foi isso. Esse é um importante paradigma que tive que entender para começar a apreciar o MySQL. Muitas vezes você vai se pegar reclamando da ausência de uma funcionalidade, ou surpreso de quão simples é a arquitetura do MySQL e a razão possivelmente venha desse paradigma.
Um desses exemplos de simplicidade é o SQL Mode, você já tentou inserir um valor em uma coluna númerica sendo que esse valor ultrapassa o limite daquele tipo de dado no MySQL?
Vamos demonstrar:
mysql; create table teste_sql_mode (col1 smallint);
Query OK, 0 rows affected (0.08 sec)
Query OK, 0 rows affected (0.08 sec)
mysql; select @@global.sql_mode, @@session.sql_mode;
+-------------------+--------------------+
| @@global.sql_mode | @@session.sql_mode |
+-------------------+--------------------+
| | |
+-------------------+--------------------+
1 row in set (0.00 sec)
+-------------------+--------------------+
| @@global.sql_mode | @@session.sql_mode |
+-------------------+--------------------+
| | |
+-------------------+--------------------+
1 row in set (0.00 sec)
mysql; insert into teste_sql_mode values (9),(99),(999),(9999),(99999),(999999), (9999999);
Query OK, 7 rows affected, 3 warnings (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 3
Query OK, 7 rows affected, 3 warnings (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 3
Perceba que apesar do comando ser executado com sucesso 3 warnings são mostrados (o problema é que dificilmente sua aplicação trata esse tipo de resultado). Quando verificamos quais são os warnings veja o resultado:
mysql; show warnings;
+---------+------+-----------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------+
| Warning | 1264 | Out of range value for column 'col1' at row 5 |
| Warning | 1264 | Out of range value for column 'col1' at row 6 |
| Warning | 1264 | Out of range value for column 'col1' at row 7 |
+---------+------+-----------------------------------------------+
3 rows in set (0.00 sec)
+---------+------+-----------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------+
| Warning | 1264 | Out of range value for column 'col1' at row 5 |
| Warning | 1264 | Out of range value for column 'col1' at row 6 |
| Warning | 1264 | Out of range value for column 'col1' at row 7 |
+---------+------+-----------------------------------------------+
3 rows in set (0.00 sec)
Lendo essa saída você imaginaria o que o MySQL fez com as três linhas inseridas que extrapolaram o limite do datatype?
mysql; select * from teste_sql_mode;
+-------+
| col1 |
+-------+
| 9 |
| 99 |
| 999 |
| 9999 |
| 32767 |
| 32767 |
| 32767 |
+-------+
7 rows in set (0.00 sec)
+-------+
| col1 |
+-------+
| 9 |
| 99 |
| 999 |
| 9999 |
| 32767 |
| 32767 |
| 32767 |
+-------+
7 rows in set (0.00 sec)
Assustador? Não, nem tanto. Como eu disse devido ao pequeno custo de performance para que o parser tenha que tratar esse e inúmeros outros casos o MySQL (por padrão) opta por esse comportamento de aceitar a maior quantidade de informações possíveis, o que pode ser útil em inúmeros casos. Assustador seria se esse comportamento fosse o único possível, mas para alterar esse comportamento para um mais tradicional é muito simples:
mysql; set session sql_mode='traditional';
Query OK, 0 rows affected (0.00 sec)
mysql; select @@global.sql_mode, @@session.sql_mode;
+-------------------+-------------------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode | @@session.sql_mode |
+-------------------+-------------------------------------------------------------------------------------------------------------------------------+
| | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER |
+-------------------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql; select @@global.sql_mode, @@session.sql_mode;
+-------------------+-------------------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode | @@session.sql_mode |
+-------------------+-------------------------------------------------------------------------------------------------------------------------------+
| | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER |
+-------------------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Agora vejam o que acontece se repetimos o teste anterior:
mysql; truncate table teste_sql_mode;
Query OK, 0 rows affected (0.00 sec)
mysql; insert into teste_sql_mode values (9),(99),(999),(9999),(99999),(999999), (9999999);
ERROR 1264 (22003): Out of range value for column 'col1' at row 5
mysql; select * from teste_sql_mode;
+------+
| col1 |
+------+
| 9 |
| 99 |
| 999 |
| 9999 |
+------+
4 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql; insert into teste_sql_mode values (9),(99),(999),(9999),(99999),(999999), (9999999);
ERROR 1264 (22003): Out of range value for column 'col1' at row 5
mysql; select * from teste_sql_mode;
+------+
| col1 |
+------+
| 9 |
| 99 |
| 999 |
| 9999 |
+------+
4 rows in set (0.00 sec)
Como vocês notaram na tentativa de inserir a quinta linha, por estar fora dos limites do datatype um erro é retornado e somente as 4 primeiras linhas são inseriras.
Esse tipo de comportamento também ocorre em vários outros momentos e na maioria das vezes passa totalmente desapercebido tanto para um usuário utilizando o cliente mysql como para uma aplicação manipulando dados no banco. Acredito que se você parar para analisar o perfil de aplicativos de utilizam como solução de banco de dados (ou de um mero repositório de informações) o MySQL vai chegar a conclusão que esse comportamento é satisfatório para a maioria dos casos, portanto é compreensivel entender o porque do parser de comandos do MySQL ter esse comportamento.
O SQL Mode representa um conjunto de regras que refina como o parser de comandos do MySQL irá tratar os comandos submetidos para ele, para ser mais claro segue abaixo uma listagem das opções de itens que podem ser configurados por essa parametrização:
- ALLOW_INVALID_DATES
- ANSI_QUOTES
- ERROR_FOR_DIVISION_BY_ZERO
- HIGH_NOT_PRECEDENCE
- IGNORE_SPACE
- NO_AUTO_CREATE_USER
- NO_AUTO_VALUE_ON_ZERO
- NO_BACKSLASH_ESCAPE
- NO_DIR_IN_CREATE
- NO_ENGINE_SUBSTITUTION
- NO_FIELD_OPTIONS
- NO_KEY_OPTIONS
- NO_TABLE_OPTIONS
- NO_UNSIGNED_SUBSTRATCION
- NO_ZERO_DATE
- NO_ZERO_IN_DATE
- ONLY_FULL_GROUP_BY
- PIPE_AS_CONCAT
- REAL_AS_FLOAT
- STRICT_ALL_TABLES
- STRICT_TRANS_TABLE
Abaixo segue outras opções que representam grupos de opções:
- ANSI
- DB2
- MAXDB
- MSSSQL
- MYSQL323
- MYSQL40
- ORACLE
- POSTGRESQL
- TRADITIONAL
Um detalhamento sobre cada uma das opções pode ser obtido na documentação do produto.
Com essa explicação e essa listagem agora podemos falar sobre como o MySQL pode trabalhar mais complacente com o Oracle (e com as principais outras tecnologias de banco de dados). Basta você configurar o SQL Mode do seu MySQL como Oracle, ao fazer isso as seguintes parametrizações são habilitadas:
mysql; set session sql_mode='oracle';
Query OK, 0 rows affected (0.00 sec)
mysql; select @@session.sql_mode;
+----------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode |
+----------------------------------------------------------------------------------------------------------------------+
| PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER |
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql; select @@session.sql_mode;
+----------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode |
+----------------------------------------------------------------------------------------------------------------------+
| PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER |
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Logicamente as diferenças entre MySQL e Oracle não são somente essas, mas com certeza se você pensa em migrar sua aplicação entre essas duas tecnologias de banco de dados isso pode lhe ajudar bastante a diminuir o tempo gasto adaptando códigos, ou ainda pior, ajudando você a evitar que um mesmo código se comporte de maneira diferente em diferentes bancos (isso acontece com grande frequência, principalmente quando falamos de controle de transações e modo de isolamento).
Um último detalhe importante é o fato dessa variável ter dois escopos distintos, ela pode ser configurada globalmente tendo assim seu valor válido para todas as sessões ou pode ser configurada somente para uma sessão específica. Essa última opção aliada com o fato dessa parametrização ser dinâmica ajuda em muito a testar a viabilidade de implementação da mesma, lembrando que quando quiser tornar essa modificação definitiva você deve alterar seu arquivo de opções my.cnf (caso você esteja em ambientes windows esse arquivo é normalmente nomeado como my.ini).
