While trying to modify Max-length of column ‘Phone’ in MySQL Database, I got this error:-
(Error Code: 1366. Incorrect string value: ‘\xE0\xA4\xAA\xE0\xA5\x8D…’ for column ‘Phone’ at row 26691)
STATEMENT:-
ALTER TABLE User_Base MODIFY COLUMN Phone VARCHAR(50) NULL;
Initially I was surprised seeing this error. But later I noticed that datatype of column was ‘NVARCHAR’ & I was trying to change it to ‘VARCHAR’. Since data was invalid for ‘VARCHAR’ type it threw the error.
ALTER COLUMN
Used to set or remove the default value for a column. Example:-
ALTER TABLE MyTable ALTER COLUMN foo SET DEFAULT ‘bar’;
ALTER TABLE MyTable ALTER COLUMN foo DROP DEFAULT;
CHANGE COLUMN
Used to rename a column, change its datatype, or move it within the schema. Example:-
ALTER TABLE MyTable CHANGE COLUMN foo bar VARCHAR(32) NOT NULL FIRST;
ALTER TABLE MyTable CHANGE COLUMN foo bar VARCHAR(32) NOT NULL AFTER baz;
MODIFY COLUMN
Used to do everything CHANGE COLUMN can, but without renaming the column. Example:-
ALTER TABLE MyTable MODIFY COLUMN foo VARCHAR(32) NOT NULL AFTER baz;
The official documentation for ALTER TABLE (for MySQL 5.1) is here.
Default CHARACTER SET of a MySQL Database is “Latin1“. When a database is created using default CHARACTER SET in MySQL, all the tables & string columns when created inherit the default CHARACTER SET of the database.
Creating Database with default CHARACTER SET is fine if we plan to store data in English language only. But when you plan to have a Multilingual Support in your application default CHARACTER SET won’t work. When you try to store CHINESE or JAPANESE characters (in Column >> Table >> Database with Default CHARACTER SET) it will silently accept those characters & show them as question marks ?????? or some boxes [][][][][][][][]. Surely this will cause frustration all round.
After digging around, the best character set to use which i found is UTF8.
To set the CHARACTER SET for the server, the my.cfg/my.ini file has to be modified:
default-character-set=utf8
Unfortunately, once a database and their tables are defined as latin1, they remain as latin1 unless you run this for each database:
ALTER DATABASE MYDATABASE CHARSET=UTF8;
and for each table:
ALTER TABLE MYTABLE CHARSET=UTF8;
and for each varchar/char type column:
ALTER TABLE MYTABLE ALTER COLUMN MYCOL CHARSET=UTF8;
and go on repeating this infinite times …. 😦
This is rather tedious and boring, so there should be a better way. And that is to dump out the SQL files, change the CHARACTER SET and dump it back in.
Under “Storage Requirements for String Types in MyISAM”, there’s a table stating that BLOB and TEXT require L + 2 bytes of storage space,
where L “represents the actual length in bytes of a given string value”, and, according to the table, is less than 2^16 (65,536) [64 KBs].
Consequently, you can’t store more than that. MEDIUMTEXT will give you 16,777,215 bytes of storage, while LONGTEXT gives you just short of 4.3 billion bytes.
Different Maximum sizes for Text & Blob Type in My SQL are:
[TEXT TYPE]
TINYTEXT – 255 bytes
TEXT – 65535 bytes [64KBs]
MEDIUMTEXT – 16,777,215 bytes (2^24 – 1) [16MBs]
LONGTEXT – 4G bytes (2^32 – 1)
[BLOB TYPE]
TINYBLOB – 255 bytes
BLOB – 65535 bytes [64KBs]
MEDIUMBLOB – 16,777,215 bytes (2^24 – 1) [16MBs]
LONGBLOB – 4G bytes (2^32 – 1)
[Reference Link: http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html]
From Code to Cloud 