mysql equivalent data types

I'm coming from a SQL Server background. What would the equivalent data types be for the following in MySQL:

NVARCHAR - provides support for international, multi-byte characters for all languages

NVARCHAR(max) - allows for very long text documen开发者_StackOverflow中文版ts

Going by http://msdn.microsoft.com/en-us/library/ms186939.aspx, I would say that

VARCHAR(n) CHARSET ucs2

is the closest equivalent. But I don't see many people using this, more often people use:

VARCHAR(n) CHARSET utf8

As far as I am aware, both charactersets utf8 and ucs2 allow for the same characters, only the encoding is different. So either one should work, and I would probably go for the latter since it is used more often.

There are some limitations in MySQL's unicode support that may or may not apply to your use case. Please refer to http://dev.mysql.com/doc/refman/5.0/en/charset-unicode.html for more info on MySQL's unicode support.

the equivalent is VARCHAR or TEXT

MySql supports nchar and nvarchar but in a different way. This is implemented using character set and collation (which is a set of rules used for comparison of strings using the character set - such as whether the comparison should be case sensitive or not).

MySql defines character set at 4 level:

Server
Database
Table
Column

character set is inherited from the immediate top level if you do not specify one. What you need to do is to ensure that the column that you want to make of type nvarchar has "character set" set to any unicode character set (utf8 is the best choice I believe) either explicitly or by inheritance.

For column level you can set "character set" as follows:

create table nvarchar_test
(
_id varchar(10) character set utf8,
_name varchar(200) character set ascii
)

In the above example _id will be able to take 10 unicode characters and _name will be able to take 100 unicode characters (each unicode character will evaluate to two ascii character)

Please go through MySql reference for further explanation.

In MySQL 5, NVARCHAR (shorthand for National Varchar) is supported and uses utf8 as the predefined character set.

http://dev.mysql.com/doc/refman/5.0/en/charset-national.html