8 bits

Some simple user defined variables in MySQL

You can use simple user defined variables in MySQL (command line). Expressions involving variables are evaluated for each row of a query result, a property that you can use to provide a column of row numbers in the output. It's a simple procedure:
set @var_name := whatever_you_want

Tax

set @vat := 1/100*119;
example: the price of a product is 45.50 USD excl. VAT. The VAT is 19%. You want to know the total price, the query is
select 45.50 * @vat;
output: 54.14

set @exvat := 1/119*100;
example: the total price of a product is 66. USD. The VAT is 19%. You want to know the price without the VAT, the query is
select 66 * @exvat;
output: 55.46

Numbered results

set @n = 0;
select @n := @n+1 as no, col1 from table1 where col1 like '%this%';

All records in the output of your query are numbered now. Nice!

Make your own user defined variable and let me know what you've achieved :-)

More information: http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

Indexes in MySQL

You can create an index on a database table:

create index index_name on table_name (field(5));

You can check your index with this command:

show index from table_name;

or

show index from dbase_name.table_name;
show index from table_name from dbase_name;

MySQL File formats and headers

MySQL uses a number of different file formats for the storage of information. Regardless of the storage engine, every MySQL table in an existing database is represented, on disk, by a .frm file, which describes the definition of the table. The .frm file is named after the table. The .frm format is the same on all platforms.

Pipe MySQL to stdin

Today I was playing with MySQL command line under Windows (I use XAMPP for this). Thanks to the MySQL cookbook I learned about the -e option. This is great, because with this -e option you can pipe MySQL to stdin, so I have learned.

For example:

c:\xampp\mysql\bin>mysql -u root -p your_password -e "select * from dbase_name.table_name" | logparser "select * from stdin" -i:tsv -o:datagrid

By default, results generated by queries that are specified with -e are displayed in tabular format if output goes to the terminal, and in tab-delimited format otherwise. This can come in handy especially when you combine MySQL with MS logparser !

Of course, you can use the -e option also for more common tasks, such as storing the output of the query in a file which you can open with Microsoft Excel, for example:

c:\xampp\mysql\bin>mysql -u root -p your_password -e "select * from dbase_name.table_name" > c:\my_files\my_output.csv

One more cool thing about the -e option is that you can use multiple queries in one line,  as long as you delimited them with ;

for example:

C:\xampp\mysql\bin>mysql -u root -p -e "use dbase_name; show tables; desc table_name; select * from table_name where col_name = 'test'"

By the way, if you let the -e precede by a -H, a nice HTML page with the results of your query will be created ! Do you prefer to create an XML page, no problemo, just let the -e precede by a -X

MySQL shortcuts

You can use several shortcuts with MySQL (command line), such as:

\? = help
\c = clear command
\C = switch to another charset (might be needed for processing multi-byte charsets)
\d = set delimiter (use something else after a statement instead of ;)
\g = send command to server
\G = send command to server
With \g and \G you can generate "vertical" output with each column value on a separate line. This makes large outputs far more readable !
\h = help
\p = print current command
\q = quit MySQL
\r = reconnect (or see which database you're in)
\R = change your mysql prompt (personalize your prompt, which can be funny :-))
\s = show status (which can be very informative)
\t = don't write to into outfile
\T = write into outfile
\u = use database
\w = don't show warnings after every statement
\W = show warnings after every statement
\# = rebuild completion hash
\. = execute an SQL scriptfile

Basic MySQL security

I have noticed that adding some security to MySQL isn't that hard.
Some security categories I have tested:
- Access protection
- Authentication
- Authorisation
- Backup

MySQL data types

I have made a list of MySQL data types for my own convenience. Maybe it's of use for somebody else too.

MySQL command line basics

I have played a bit with MySQL command line, which is available after downloading and installing XAMPP. Start the MySQL command line via a dosbox, browse to c:\xampp\mysql\bin and type mysql -u root.

Show databases

command: show databases;

Select a database

command: use name_of_database;

Show tables

command: show tables;

Describe the schema of a table

command: desc name_of_table;

Basic command

command: select * from name_of_table;

Create index

create index part_of_name on table1 (col1(255));

Basic inner join command

select table1.col1,table1.col2,table2.col1,table2.col2 from table1,table2 where table1.id=table2.id;

Inner joins works better when an index has been created on both tables!

More commands

Of course, this is just a nutshell and there are far more possibilities with MySQL. You can find a great reference at http://dev.mysql.com/doc/refman/5.0/en/

How to change security settings for XAMPP localhost

The standard settings for XAMPP MySQL are without a root password. You can set a password for the root account here:

http://localhost/security/xamppsecurity.php

Afterwards, you can check it here:

http://localhost/security/

MySQL case (in)sensitive

Vandaag is mij gebleken dat MySQL onderscheid kan maken tussen case sensitive en case insensitive. Ik kwam hier toevallig achter toen ik bezig was met de bouw van een inlogmodule. Met andere woorden, je kunt MySQL de opdracht geven om case sensitive te zijn, waardoor gebruikersnamen exact moeten worden ingevoerd om toegang te kunnen krijgen. Mocht je MySQL als case insensitive ingesteld hebben, dan is het mogelijk om met verschillende schrijfwijzes van gebruikersnamen in te loggen (gebruikersnaam, GEBRUIKERSNAAM, GeBrUiKeRsNaAm etc.) en dit kan sommige serverprocessen aardig in de war doen belanden.