Database archive at MyTwentyFive.com

Archive for the 'Database' Category

MySQL Varchar Can Now Contain More Than 255 Characters

Published January 3, 2009 in Database. 0 Comments

Found an interesting new feature for MySQL when I was randomly reading the MySQL 5.0.x change logs.

VARCHAR columns now remember end space. A VARCHAR() column can now contain up to 65535 bytes.

Prior to MySQL 5.0.3, varchar can only contain up to maximum of 255 characters. However, from MySQL5.0.3 and onwards, we can now declare varchar with more than 255 characters.

Don’t Exceed The 65,535 bytes Maximum Row Size

However, do note that, the maximum row size for MySQL is 65,535 bytes, shared among all columns within a row. Which also means, if you have more than 1 column in a table, declaring a column with varchar(65,535) is not feasible. Besides, UTF8 characters can also require up to 3 bytes per character.

Trailing Space Will Be Retained

Prior to MySQL 5.0.3, any trailing space of a string will be removed when it’s stored as varchar. But beginning from MySQL 5.0.3 and later, varchar is handled with better conformance to standard - trailing space will be retained.

MySQL Table Names could be Case Sensitive in Unix

Published October 22, 2008 in Database and Tips & Tricks. 0 Comments

Something notable about MySQL case sensitivity that my project team has encountered last weekend. On Windows and Mac OS X, all object identifiers (such as names of database, table, column, index and etc) are case insensitive.

So, even if someone doesn’t practice consistency of identifier naming on Windows and Mac OS X, you will not encounter table 'tbl_name' doesn't exist error, as long as spelling is correct.

However, if you have a MySQL table named as PRODUCT_ITEMS, you will encounter table 'product_items' doesn't exist error on most varieties of Unix and Linux, when the following select statement is executed.

select * from product_items;

Such a simple select statement isn’t it? But will fail in Unix and Linux as database names, table names and table aliases are case sensitive by default.

Solution 1: Ensure Identifier Naming Consistency

The most elegant solution, is of course to ensure identifier naming consistency in database and codes. But if you’re working on a mammoth project, this is not the quickest solution though.

Solution 2: Alter lower_case_table_names System Variable

The quickest solution in this case, is to alter the database default behavior, such that table names are always treated as case insensitive.

By default, lower_case_table_names is set to ‘0′ in Unix. So, you can do the following to alter the lower_case_table_names variable.

Ensure that the database name and all existing table names are lowercase.
Open to edit /etc/my.cnf file
Add “lower_case__table_names=1” (without quote) into /etc/my.cnf
Restart MySQL

After restart, the lower_case_table_names variable of your MySQL database should appear as ‘1‘. So, regardless of the case of identifier naming in your codes, all should work like case sensitivity is never an issue at all.

Refer to http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html for further information.