Novo Site

Amigos Demorei a postar novos artigos, porque agora tenho um novo site

www.talibamartins.com

Todas as dicas estao no novo Blog.

Diferença entre datas

Ajudando o Amigo tevez hj, ele precisava saber a diferença de dias entre 2 datas.

mysql> SELECT DATEDIFF(CURDATE(),’2007-01-01′);

A função DATEDIFF() retorna um inteiro com o intervalo. a query acima retorna:

mysql> 274

A query abaixo:
mysql> SELECT DATEDIFF(‘2007-01-01′,CURDATE());
mysql> -274

Ajudas – Selecionar registros em dias úteis

Ajudando o Jorge, vai um post sobre como listar os registros com data em dias úteis, de segunda a sexta.
A função WEEKDAY() traz o dia da semana, ela retorna um inteiro correspondente ao dia.

0 – Segunda
1 – Terça
2 – Quarta
3 – Quinta
4 – Sexta
5 – Sabado
6 – Domingo

Sendo assim nossa consulta fica:

mysql> SELECT tbl_data FROM tb_table WHERE WEEKDAY(tbl_data) NOT IN (5,6) ;

[]’s Jorge

Pesquisar pelo Ano

Seguindo a serie “BUSCAS”, alguem chegou ao blog buscando “mysql pesquisa por year”. vamos à solução:

mysql> SELECT * FROM tb_table where YEAR(tbl_data)=’2007′

Essa consulta traz todos os registros com ano = 2007, é claro o campo deve ser DATETIME,DATE ou TIMESTAMP

té

Perguntas e respostas sobre o Falcon (texto Gringo)

1. What is Falcon?

Falcon is a transactional data storage engine that runs on all popular hardware/architecture platforms. Its design takes advantage of the large memory caches available in 64-bit environments. Falcon will be the transaction management engine of choice in MySQL for businesses that need fast transaction performance and rock-solid data reliability.

2. How did the Falcon project begin?

Falcon came from MySQL’s February 2006 acquisition of Netfrastructure, a company owned by Jim Starkey and Ann Harrison, who now work for MySQL on the Falcon effort. The Falcon group adapted the Netfrastructure data repository into a MySQL pluggable storage engine.

3. Is Falcon a replacement for InnoDB?

Falcon was not designed to be a plug-in replacement for InnoDB. New applications that would have been implemented with InnoDB can use Falcon easily. Many applications that currently use InnoDB should be able to also use Falcon. Reasons for not switching to Falcon from InnoDB would be the absolute reliance on clustered indexes, different locking needs, and the need to use statement-based replication in Falcon.

4. Does Falcon support ACID transactions?

Yes, Falcon is ACID compliant. Falcon manages transactions through a multi-generational approach in which only committed data is stored in the database. Uncommitted data and old versions of data normally exist only in the record cache. Record versions created by large insert and update transactions may be offloaded into the serial log. One exception is that Falcon creates blobs larger than a page directly on database pages, bypassing the log to avoid writing large objects twice.

5. Does Falcon handle crash recovery?

Yes, Falcon handles crash recovery through the use of its serial log files.

6. How does Falcon use its log files?

Falcon log files contain entries for committed data to be copied into the database, and entries that allow it to redo actions during recovery, and entries that allow it to undo partial changes made by failed transactions. The serial log is a combination do, redo, and undo log, with some entries being used in more than one phase. Falcon alternates between two log files, switching when all entries in the older file have been moved into the database file.

7. Does Falcon support all standard MySQL datatypes?

Yes.

8. How does locking work in Falcon?

Falcon uses MVCC (multi-version concurrency control) to provide record level concurrency. This means that readers don’t block writers and vice-versa.

9. What types of indexes does Falcon support?

Falcon uses a form of B-tree indexing with a two-stage retrieval. The entries in the B-tree are prefix-compressed and have trailing blanks and zeros truncated. Two-stage retrieval provides some of the benefits of clustered indexes without the drawbacks that come from the use of such structures. Traditional database index implementations traverse indexes by bouncing between index pages and database pages, which can oftentimes lead to inefficient or costly disk access. Clustered indexes (or index-organized tables) are structured so that the physical ordering of records corresponds to the index order, with the actual leaf pages being the data pages. While some applications benefit from this organization, the physical implementation of clustered indexes can lead to space management problems, such as page splitting. Furthermore, a table can be clustered on only one index, reducing the efficiency of secondary indexes. In Falcon, the index is scanned first, with bits being set in a sparse bit vector to indicate selected records. Records and data pages are then processed in bit order, which is also physical order on disk.

Falcon’s indexing scheme results in a number of benefits. First, all indexes behave almost like well-tuned clustered indexes. Second, index pages are locked, read, and released, with no intervening data accesses that can cause locking conflicts.

10. Does Falcon have Foreign Keys/Server-enforced Referential Integrity?

Foreign key support in Falcon will be supplied above the storage engine layer, which means that other engines besides Falcon will be able to use server-enforced referential integrity. The Foreign Key work is being done outside of the Falcon project and is not available in MySQL 6.0.

11. What is the performance of Falcon like?

Falcon excels in processing short to medium-sized transactions on multi-CPU hardware, and is therefore ideal for most online database applications.

12. Does Falcon work with normal MySQL replication?

Falcon only works with row-based replication.

13. What limits does Falcon currently have?

Current limits include:

• A single database limit is 110TB.
• Each table is limited to 32,000 columns.
• Each table is limited to 4 billion rows (will be removed in GA).

14. What platforms are supported in the Falcon Alpha?

Linux, Windows, and Mac Intel. Others will soon follow.

15. What V1 GA features are missing in the Falcon Alpha?

• Two-phase commit
• Configurable lock timeout
• Additional platform support

16. When will Falcon be GA?

The plan is to have a GA release of Falcon in mid-2008, but is dependent on the information gathered from the alpha and beta testing periods.

10 razões para usar o Mysql 6 – Falcon em aplicações online (Texto em inglês)

Top Reasons to use Falcon for Online Applications

1. ACID Transactions and Crash Recovery

The Falcon engine supplies full ACID (atomic, consistent, isolated, durable) transaction control coupled with crash recovery so no critical data is ever lost.

2. High-Performance Transactions

Falcon manages all transactional activity in memory (with optional paging to disk if necessary) so all transactions are serviced in the quickest time possible. In addition, rollbacks of even the largest transactions are accomplished in a near instantaneous manner.

3. High Concurrency

Falcon’s MVCC and row-level concurrency design mean readers don’t block writers and vice-versa, with the end result being that lock contention issues are extremely rare.

4. Online, Automatic Storage Management

Falcon utilizes tablespaces for user data storage with there being no practical limit to how many tablespaces can be created and used to manage tables, indexes, and BLOB data. All tablespaces feature auto-extending datafiles, automatic space reclamation, and compaction of data pages.

5. Unique Caching Strategy

Falcon sports a distinct memory cache called the Record Cache that differs from traditional database caches in that it only holds the necessary rows being requested by end user queries instead of full database pages that may or may not have data being asked for via SQL queries. This equates to more efficient memory utilization plus it ensures needed data stays hot/resident in memory all the time.

6. High-Performance Indexes

Falcon utilizes special B-tree indexes that always read in physical disk order, which minimizes I/O response time. Multiple indexes can be created on a Falcon table with all indexes servicing requests in this fashion. Indexes also have prefix and suffix compression to reduce I/O load further.

7. Comprehensive Performance Diagnostics

Troubleshooting and tuning Falcon performance is easy via a number of diagnostic tables that provide a window into memory usage, I/O performance, transactional activity, lock activity, and much more.

8. Simplified Configuration

There is no complexity whatsoever in terms of configuration as only a handful of variables exist to control the behavior of the Falcon engine.

9. High Availability

Extreme degrees of high availability are easily accomplished for a Falcon-driven system by using either MySQL replication or supported third-party high availability solutions such as DRBD.

10. Parallel Execution

Falcon’s design takes advantage of multi-core systems to provide parallel execution of user and service threads. Falcon uses fine-grained multi-threading to increase parallelism with locking on internal structures being done at a low level. In some cases, two threads can change different attributes of the structure at once, because the attributes are separately lockable.

Funções de arredondamento

O mysql dispõe de varias funções de arredondamento:

CEILING – Arredonda para cima
mysql> SELECT CEILING(2.5) ;
mysql>3

FLOOR – Arredonda para baixo
mysql> SELECT FLOOR(2.5) ;
mysql>2

ROUND – Arredonda para o proximo inteiro
mysql> SELECT ROUND(2.51) ;
mysql>3

TRUNCATE – Elimina as casas decimais
mysql> SELECT TRUNCATE(2.5) ;
mysql>2

A Função IN

Muitas vezes precisamos fazer uma consulta, onde uma string ou inteiro devem estar entre 2 ou mais valores, por exemplo, preciso listar todos os clientes com cep = ‘79004-580′,’82020-050′,’80010-060′ ou ‘11010-040′. Se você nao conhece a função IN logo pensaria em usar OR, mas não é necessário tanto, vamos à query:

mysql> select * from tb_cliente where cln_cep IN (‘79004-580′,’82020-050′,’80010-060′,’11010-040′);

Teta …

Inserindo e alterando registros

Poucos sabem, mas existe no mysql  2 maneiras de fazer insert caso nao exista o registro e update caso exista, REPLACE e INSERT … ON DUPLICATE KEY UPDATE. A syntaxe é seguinte:

mysql> REPLACE tb_table (tbl_id,tbl_name) VALUES (‘1′,’Jão’);
Caso ja exista a chave ‘1′ o REPLACE apaga a linha e insere um novo registro com o ID 1

mysql> INSERT tb_table (tbl_id,tbl_name) VALUES (1,’Jão’) ON DUPLICATE KEY UPDATE tbl_name=’Jão’
Ao contrário da função REPLACE, ON DUPLICATE KEY UPDATE,  nao apaga a linha, e sim faz apenas o UPDATE  do campo especificado.

O Uso de REPLACE é mais simples, porém, como ele elimina a linha para inserir novos registros, caso vc nao passe algum campo este ficará vazio, e no uso de ON DUPLICATE KEY UPDATE,  voce escolhe quais serão os campos afetados.

Com o uso de REPLACE, é possivel saber qual foi a operação (Insert ou Update) , pelo numero de ROWS afetados, 1 – INSERT , 2 – REPLACE porque ele deleta antes de inserir.

Té outra!!!

BackUp – mysqldump

O Tiago, grande pnc, pediu uma ajuda sobre dump, vamos la:
O mysqldump converte a estrutura e os dados de uma tabela em comando sql. voce pode fazer o dump de uma tabela, do banco todo, ou de todos os bancos do servidor.

shell-> mysqldump database > scriptdesaida.sql

Veja algumas opções do mysql dump:

–add-drop-database -  Adiciona ‘DROP DATABASE’ antes de criar o banco.
–add-drop-table        – Adiciona  ‘drop table’ antes de criar a tabela.
-i, –comments  – Insere comentarios adicionais ao script
-f, –force Força o mysqldump a continuar mesmo havendo erros.
-h, –host=name para fazer um dump remoto aqui vai o ip do servidor.
-p, –password[=name], senha do usuarioque vc esta usando
-u, –user=name nome do usuario.
-X, –xml gera um dump em xml.