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 documents
-
1from what I read, nvarchar(n) can store 1 through 4,000 characters - not really my definition of "very long text documents". Sure you meant that?Roland Bouman– Roland Bouman2010-02-09 21:14:13 +00:00Commented Feb 9, 2010 at 21:14
-
NVARCHAR(MAX) is for the long stuff. Not VARCHAR(n).Adrian Lynch– Adrian Lynch2014-10-09 09:15:49 +00:00Commented Oct 9, 2014 at 9:15
Add a comment
|
3 Answers
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.
2 Comments
AJ Dhaliwal
Should be using utf8mb4 nowadays to represent a wider range of characters and emoticons. See this answer
Jing He
the utf-8 encoding in mysql seems not the real utf-8, which only contains limited number of characters... should use utf8mb4 instead
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.
Comments
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
