Jaslabs: High performance Software

By Justin Silverton

These are some tips that may help you out when dealing with mysql tables (known in 4.1 and below).

1) char and varchar are case sensitive

example:

if you have a table that contains the following:

table newtable (
name varchar(32)
)

name contains the name “John Smith”.

the following statement: “SELECT * from newtable where name=’john smith’ will return our record.

to stop this from happening, use the following when you create your table:

CREATE TABLE newtable (
name VARCHAR(32) BINARY
)

2) Varchar type is limited to 255 characters

3) Varchar trailing spaces are stripped

example: insert into newtable values(’Test with no spaces ‘);

select concat(name, ‘no spaces’) FROM newtable;

output will be: Test with no spacesnospaces

Varchar works this way, because it saves space by stripping the spaces.

if you need to keep the trailing spaces in the data you are adding to a varchar type,
you need to use the text or blob types.

4) operator

The (or) operater is a logical operator

example: select ’string1′ ’string2′ will not return ’string1string2′

5) function parameters

This issue has caused me many headaches in the past, and I am not sure why this issue was never fixed. If there is a space
between the paramater list and an internal function that you want to execute, it will return an error.

example: select min (my_field) from mytable wil return an error, while select min(my_field) from my_table will not