Within an instance to identify the default character sets and collation in all databases.
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;
To alter the default character sets and collations for a database in a MySQL or MaraiDB instance.
ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;
Creating Table – Template SQL
CREATE TABLE table1
(col1 INT(10) NOT NULL AUTO_INCREMENT,
col2 CHAR(30),
PRIMARY KEY (col1))
ENGINE = INNODB;
And a good source of data types are here
Tech on the Net
There are unconfirmed reposts that queries against TEXT fields are generally 3 times slower than against VARCHAR fields.
Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.
VARCHAR(MAX)
Is a thing
And if you want to change the engine on a table retrospectively. See OPERATIONS.
And a short note on your choice of collation
From Stack overflow here
Actually, you probably want to use utf8_unicode_ci or utf8_general_ci.
utf8_general_ci sorts by stripping away all accents and sorting as if it were ASCII
utf8_unicode_ci uses the Unicode sort order, so it sorts correctly in more languages
However, if you are only using this to store English text, these shouldn’t differ.
Note further down there is a person that states “be very aware of this problem that can occur using utf8_general_ci
“MySQL will not distinguish between some characters in select statements, if the utf8_general_ci collation is used. This can lead to very nasty bugs – especially for example, where usernames are involved. Depending on the implementation that uses the database tables, this problem could allow malicious users to create a username matching an administrator account.
This problem exposes itself at the very least in early 5.x versions – I’m not sure if this behaviour as changed later.”
And here is some code that allowed me to reset the auto increment index on a table.
ALTER TABLE my_table MODIFY COLUMN pkid INT(10) UNSIGNED;
COMMIT;
ALTER TABLE my_table MODIFY COLUMN pkid INT(10) UNSIGNED AUTO_INCREMENT;
COMMIT;
I have tested it and works with INNODB and MySQL
Another way to do it might be to drop the pkid table and then recreate it again
Using
ALTER TABLE mytable DROP pkid;
ALTER TABLE mytable ADD pkid INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
