Adding column field, please help

[jokerfool]

This is my current database with MySQL, everything works really well and submitting data to it works really well.

CREATE DATABASE bril_mx;

use test;

CREATE TABLE users (
	id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
	firstname VARCHAR(30) NOT NULL,
	email VARCHAR(50) NOT NULL,
	age INT(3),
	location VARCHAR(50),
	date TIMESTAMP
);

However, I would now like to add an additional column field called username.

Is this the correct procedure to add username?

ALTER TABLE test
ADD UserName varchar(255);

Thank you.

[jmcilhinney]

What happened when you tried it?

[jokerfool]

I wasn't sure where to enter the info but I am sure it goes into SQL

[jokerfool]

It all came back with content in column saying NULL

I just ran

ALTER TABLE clients2
DROP COLUMN discord;

Dropped that column.

[jmcilhinney]

It all came back with content in column saying NULL

If you're saying that you successfully added the column and that every row contained NULL in that column then that's exactly what you should expect. Why would adding a column magically add data to any rows? NULL is ALWAYS the default for any row in any column unless you specify otherwise.

[jokerfool]

I didn't want it or didn't expect it to add data, I was just surprised it said NULL instead of being empty which the others were when I first started.

So how would I add the username after the firstname?

[jmcilhinney]

I didn't want it or didn't expect it to add data, I was just surprised it said NULL instead of being empty which the others were when I first started.

You've actually got that slightly backwards. If it says NULL then it actually is empty, i.e. NULL means no value. If it is being shown as blank in a management tool then that actually means that it does have a value and that value is an empty string. It's like in VB where Nothing means no value and an empty String actually is a value. It's the value itself that is empty, i.e. the string contains no characters. The reason your other columns don't show NULL is because you have expressly specified them as not being able to be NULL.

I'm not sure about MySQL but SQL Server will not allow you to add a new column specified as NOT NULL because it defaults to NULL in each row. You may be able to do so if you specify a default but I'm not sure as I've never tried. In SQL Server, if you want a new column that is NOT NULL then you have to add it as nullable first, then add the data with an UPDATE on each row, then modify the column to make it NOT NULL. MySQL may be the same or it may allow you to add the column as NOT NULL and set an empty string in each row automatically.

[GaryMazzone]

Why do you care where the field is located (positioned) in the table it does not matter where it is. If you want it to show up after firstname in the return set then specify it like that in the SQL SELECT statement …
SELECT firstname,username,.... FROM users;


If you insist on it being in the table positioned after firstname then you would need to create a new table named users_xxx with the columns in the order you want, copy all data from the users table to users_xxx, drop users table rename the users_xxx to users.