Should i be using nvarchar in my db?

#1

@astonecipher mentioned stored procedures in a recent post, so i began reading about them. Anyway, i saw an example where the person set a column as username nvarchar(512). I though ?nvarchar? i understand that this is unicode where varchar is ascii. I live in Germany where it is possible that a user types in Turkish, Russian or Polish in addition to English and German. I am wondering if username should be varchar and friendly display name should be nvarchar. But an article mentioned that varchar requires conversion to unicode because the database and the os use unicode natively. Thus, the use of varchar is slower when dealing with a username? should i just make both nvarchar? if so, how large should it be to accomodate a hashed password?

point: i recently changed my db so that a username is a unique key. when i did this, the varchar(255) became an error. mySQL reported that the column exceeded allowable bytes. I reduced the username to varchar(127) and it accepted the change. I need a better understanding of bytes and mysql storage and limitations, yes. However, if i have password varchar(255) then what should i do if i implement nvarchar? double it? nvarchar(512) to accomodate 16-bits?

Thank you.

#2

As i look at the documentations the nvarchar type is used within SQL-Server and there is no use in MySQL. But anyway i would recommend to use the varchar type with charset utf8 or utf8mb4 and let the application decide what to do - if you have no possibility to use real constraints like in PostgreSQL. Maybe you just take some Regex to clean up the username (and of course check if it’s UNIQUE).

varchar 255 should be good for passwords as they do not have to be unique and if you use bcrypt for the hashing you end up with about 60 characters inclusive salt.

greetings from Hannover.

1 Like
#3

Thank you, chorn :slight_smile:

how foolish of me? i didn’t look at the whole tutorial to notice the database being used. I jumped straight to the stored procedure code. I’m trying to introduce myself to stored procedures. I should have noticed sql-server. My apologies.

Greetings from Berlin! Hannover is not too far from here. :smile:
it’s nice to know that Germany is well represented in the forum. I doff my hat to you.