Some Important Attributes in Mysql

There have some most important attributes in mysql which can solve/get the solution of our faced scenery. So, we should keep in mind that those we can do by using default attribute of mysql.


BINARY Attribute

All character data types have a collation that is used to determine how characters in the field are
compared. By default, a character field’s collation is case insensitive. This means that, when you sort the column alphabetically (which you learn to do shortly), “a” comes before both “b” and “B”. It also means that queries looking for the text “banana” will match the field values “banana” and “Banana”.

However, by adding the BINARY attribute after the data type definition, you switch the field to a binary collation, which is case sensitive; when sorting, “a” comes before “b”, but “B” comes before “a” (because, generally speaking, uppercase letters come before lowercase letters in a character set). Furthermore, this means that matches are case sensitive too; “banana” will only match “banana”, not “Banana”. In this case, you created the username field of the members table with the BINARY attribute, making it case sensitive:

username VARCHAR(30) BINARY NOT NULL UNIQUE,

This ensures that there’s no ambiguity over the case of the letters in each user ’s username; for example,
“shaon” is a different username than “Shaon”. This is important because many people choose usernames where the case of the username’s characters is significant to them. If they created their account with a username of “shaon”, and later found out they could also login using “Shaon”, they might wonder if they were working with one account or two!

ENUM Data Type

Enumeration columns is a  type of string column where only predefined string values are allowed in the field .
Suppose we want user can input only 'm' or 'f' for their gender then we have to use:

gender ENUM ('m', 'f'),

or their favorite subject to read:

subject ENUM( 'JAVA','PHP','CEH'),

TIMESTAMP Data Type

Another most important data type is TIMESTAMP data type which can get the result exactly what you need. It is a bit different from the other date/time types in that it can automatically record the time that certain event occur. For example, when you add a new row to a table containing a TIMESTAMP column, the field stores the time that the insertion took place. Similarly, whenever a row is updated, the TIMESTAMP field is automatically updated with the time of the update.

The other point to remember about TIMESTAMP fields is that they store the date and time in the UTC
(Universal Coordinated Time) time zone, which is essentially the same as the GMT time zone. This
probably won’t affect you much, because MySQL automatically converts TIMESTAMP values between UTC and your server ’s time zone as required. However, bear in mind that if you store a TIMESTAMP value in a table, and you later change the server ’s time zone, the value that you get back from the TIMESTAMP field will be different.

A TIMESTAMP field is great for tracking things such as when a record was last created or updated,
because you don’t have to worry about setting or changing its value; it happens automatically. In this
example, you created a TIMESTAMP field in the accessLog table to track when the last access was made:

lastAccess TIMESTAMP NOT NULL,