MYSQL / MariaDB – Useful SQL in both creating New Tables and Clarifying Default Parameters

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;

Connect MS Access 2003 to MySQL

It is an incredible feature of MS Access that it is so easy to connect to different databases and use as a Management Studio. Having a unified platform across all the different backends is very very useful. Setting up those connections is not always straight forward and as ever involves configuration – something which often evades all but the most accurate of intelligent guesswork. This is set out for MS Access 2003 but I would expect this to work on all versions of MS Access.

Use the architect version of the MySQL driver that relates to the version of MS Access that you are using. In this case MS Access 03 is 2003 so I used this.

MySQL ODBC drivers at August 2017

Install as per normal driver.

Then open up ODBC Data Source Administrator – I have two options here 32 bit and 64 bit – its not clear if there is a difference but I have been choosing the 32 bit version – Navigate to file DSN and then hit Add…

You will be presented with a Create New Data Source window – navigate down to the MySQL and here choose unicode – ANSI and Unicode are two character encodings that were historically in wide use. Ansi is very old and is used by older operating systems like Windows 98. Unicode is newer which has a vast character set and is used by Office 2003 and upwards. UTF-8 is even newer.

Select then hit the next button.

You will be presented with a Create New Data Source dialog in which you can hit the Browse button and create the name of your file dsn. Here I have already created three – 2 are SQL Azure dsns and the third is a link to my inspirunner database. A hosted account.

You should then be shown the MySQL Connector / ODBC

You absolutely must know the name of your server – the port it is listening on and the user password – once these are filled in selecting the downard arrow should allow you to select the database.

Hit test and you should see success and then hit OK.

Now simply go into MS Access 2003 as per usual right click in the tables window and select the file dsn from the location you stored it in – you should be given all the tables from which to select