ソフトウェアエンジニアの技術ブログ：Software engineer tech blog

CREATE TABLE google.markers (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(60) NOT NULL,
	address VARCHAR(80) NOT NULL,
	lat FLOAT(10, 6) NOT NULL,
	lng FLOAT(10, 6) NOT NULL,
	type VARCHAR(30) NOT NULL
) ENGINE = MYISAM;

INSERT INTO markers (`name`, `address`, `lat`, `lng`, `type`) VALUES ('Love.Fish', '580 Darling Street, Rozelle, NSW', '-33.861034', '151.171936', 'restaurant');
INSERT INTO markers (`name`, `address`, `lat`, `lng`, `type`) VALUES ('Young Henrys', '76 Wilford Street, Newtown, NSW', '-33.898113', '151.174469', 'bar');
INSERT INTO markers (`name`, `address`, `lat`, `lng`, `type`) VALUES ('Hunter Gatherer', 'Greenwood Plaza, 36 Blue St, North Sydney NSW', '-33.840282', '151.207474', 'bar');
INSERT INTO markers (`name`, `address`, `lat`, `lng`, `type`) VALUES ('The Potting Shed', '7A, 2 Huntley Street, Alexandria, NSW', '-33.910751', '151.194168', 'bar');
INSERT INTO markers (`name`, `address`, `lat`, `lng`, `type`) VALUES ('Nomad', '16 Foster Street, Surry Hills, NSW', '-33.879917', '151.210449', 'bar');
INSERT INTO markers (`name`, `address`, `lat`, `lng`, `type`) VALUES ('Three Blue Ducks', '43 Macpherson Street, Bronte, NSW', '-33.906357', '151.263763', 'restaurant');
INSERT INTO markers (`name`, `address`, `lat`, `lng`, `type`) VALUES ('Single Origin Roasters', '60-64 Reservoir Street, Surry Hills, NSW', '-33.881123', '151.209656', 'restaurant');
INSERT INTO markers (`name`, `address`, `lat`, `lng`, `type`) VALUES ('Red Lantern', '60 Riley Street, Darlinghurst, NSW', '-33.874737', '151.215530', 'restaurant');

where 1 は無条件で全データを抽出という意味。

mysql> select * from markers where 1;
+—-+————————+———————————————–+————+————+————+
| id | name | address | lat | lng | type |
+—-+————————+———————————————–+————+————+————+
| 1 | Love.Fish | 580 Darling Street, Rozelle, NSW | -33.861034 | 151.171936 | restaurant |
| 2 | Young Henrys | 76 Wilford Street, Newtown, NSW | -33.898113 | 151.174469 | bar |
| 3 | Hunter Gatherer | Greenwood Plaza, 36 Blue St, North Sydney NSW | -33.840282 | 151.207474 | bar |
| 4 | The Potting Shed | 7A, 2 Huntley Street, Alexandria, NSW | -33.910751 | 151.194168 | bar |
| 5 | Nomad | 16 Foster Street, Surry Hills, NSW | -33.879917 | 151.210449 | bar |
| 6 | Three Blue Ducks | 43 Macpherson Street, Bronte, NSW | -33.906357 | 151.263763 | restaurant |
| 7 | Single Origin Roasters | 60-64 Reservoir Street, Surry Hills, NSW | -33.881123 | 151.209656 | restaurant |
| 8 | Red Lantern | 60 Riley Street, Darlinghurst, NSW | -33.874737 | 151.215530 | restaurant |
+—-+————————+———————————————–+————+————+————+
8 rows in set (0.00 sec)

floatは4バイト、Dobuleは8バイト

mysql> CREATE TABLE google.float_test (
    -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> num FLOAT(10, 2)
    -> ) ENGINE = MYISAM;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO float_test (num) VALUES (1.0), (1.1),(1.11),(1.111);
Query OK, 4 rows affected (0.13 sec)
Records: 4  Duplicates: 0  Warnings: 0

データを確認

mysql> select * from float_test;
+----+------+
| id | num  |
+----+------+
|  1 | 1.00 |
|  2 | 1.10 |
|  3 | 1.11 |
|  4 | 1.11 |
+----+------+
4 rows in set (0.00 sec)

CREATE TABLE google.float_test (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	num FLOAT(6, 2)
) ENGINE = MYISAM;

INSERT INTO float_test (num) VALUES (1.0), (10.1),(100.11),(1000.111);

mysql> select * from float_test;
+----+---------+
| id | num     |
+----+---------+
|  1 |    1.00 |
|  2 |   10.10 |
|  3 |  100.11 |
|  4 | 1000.11 |
+----+---------+
4 rows in set (0.00 sec)

どうやらfloat(n.y) のnは全体の桁数、yは小数点以下の桁数ですね。

tableを作ります。

CREATE TABLE google.MYISAM (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR (60) NOT NULL
) ENGINE = MYISAM;

CREATE TABLE google.NONE_MYISAM (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR (60) NOT NULL
);

mysql> show tables;
+------------------+
| Tables_in_google |
+------------------+
| MYISAM           |
| NONE_MYISAM      |
+------------------+
2 rows in set (0.00 sec)

こちらのコマンドで、ストレージエンジンにMYISAMが使われているか、確認できます。

mysql> SELECT
    -> TABLE_SCHEMA,
    -> TABLE_NAME,
    -> ENGINE
    -> FROM
    -> information_schema.TABLES
    -> WHERE
    -> TABLE_SCHEMA NOT IN(
    ->
Display all 770 possibilities? (y or n)
    -> 'information_schema',
    ->
Display all 770 possibilities? (y or n)
    -> 'performance_schema',
    ->
Display all 770 possibilities? (y or n)
    -> 'mysql'
    -> );

以下のように、MYISAMを指定しない場合は、InnoDBのエンジンが使われていますね。

mysql> drop table MYISAM, NONE_MYISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

MYSAMとは、ストレートエンジンのこと。データの保存処理を行っており、MySQLでは「InnoDB」と「MyISAM」が有名。
MySQL5.5以上は、InnoDBがデフォルト。

違いは、InnoDBは対象のレコードに対してロックを行い、MyISAMは対象のテーブルに対してロックを行う。

まず、バージョン確認。

[vagrant@localhost map]$ mysql -v
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Reading history-file /home/vagrant/.mysql_history
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6.34です。

続いて、databaseを作ります。

create database google;
use google;

ここで、テーブルを作る際に、MYISAMを指定します。

CREATE TABLE google.makers (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR (60) NOT NULL
) ENGINE = MYISAM;