wiki/examples: update table name to fix identifier case sensitivity bug

[liuzengh]

Environment

mysql offical docker image mysql:9.3.0 from docker hub https://hub.docker.com/_/mysql

Steps to Reproduce

1. follow this iwiki example: https://github.com/go-sql-driver/mysql/wiki/Examples#prepared-statements to create database and table at init.sql

CREATE DATABASE IF NOT EXISTS my_database;
USE my_database;

CREATE TABLE IF NOT EXISTS squareNum (
    number INT(11) NOT NULL,
    squareNumber INT(11) NOT NULL,
    PRIMARY KEY (number)
);

2. execute following run-docker.sh script

#!/usr/bin/env bash
docker pull mysql:9.3.0
docker stop mysql-container
docker rm mysql-container || true

docker run --name mysql-container \
  -e MYSQL_ROOT_PASSWORD=my-secret-pw \
  -d -p 3306:3306 \
  -v "$(pwd)"/init.sql:/docker-entrypoint-initdb.d/init.sql \
  mysql:9.3.0

3. follow this iwiki example: https://github.com/go-sql-driver/mysql/wiki/Examples#prepared-statements to run main.go

Actual Result

stdout:

panic: Error 1146 (42S02): Table 'my_database.squarenum' doesn't exist

goroutine 1 [running]:
main.main()
        /Users/amdahliu/GolandProjects/trpc-database/mysql/examples/go-sql-driver-mysql/main.go:36 +0x3ec
exit status 2

Expected Result

stdout:

The square number of 13 is: 169
The square number of 1 is: 1

This means such names are not case-sensitive in Windows, but are case-sensitive in most varieties of Unix. One notable exception is macOS, which is Unix-based but uses a default file system type (HFS+) that is not case-sensitive.

Cause

In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). Triggers also correspond to files. Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database, table, and trigger names. This means such names are not case-sensitive in Windows, but are case-sensitive in most varieties of Unix. One notable exception is macOS, which is Unix-based but uses a default file system type (HFS+) that is not case-sensitive. However, macOS also supports UFS volumes, which are case-sensitive just as on any Unix.

• https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html

Solution

Please see commit https://github.com/liuzengh/mysql/wiki/Examples/c50ef34b3b6c616c0119bd7dabf3dbee076732c8, I can't find a way to to make pull request for wiki repo.

Change List:

• Rename squareNum table to square_num
• Rename squareNumber column to square_number for better readability and consistency
• Add newline characters in print statements for proper output formatting.

[methane]

Do you really confirm that is caused by case sensitivity?

+--------------+---------+------+-----+---------+-------+
| Field        | Type    | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| number       | int(11) | NO   | PRI | NULL    |       |
| squareNumber | int(11) | NO   |     | NULL    |       |
+--------------+---------+------+-----+---------+-------+

and

	stmtIns, err := db.Prepare("INSERT INTO squareNum VALUES( ?, ? )") // ? = placeholder

This example has squareNumber vs squareNum issue.
Replacing squareNum with squareNumber does not fix?

[liuzengh]

Replacing squareNum with squareNumber does fix(I think this is caused by case sensitivity, what do you think? typo?).
The table name squareNum, squarenum in the two SQL statements should be exactly the same, i change them to square_num.