What is mysql data type of Oracle “NUMBER”?

Oracle data type: NUMBER
The “NUMBER” data type stores fixed and floating point numbers. It can store virtually any numerical value. If your system is running Oracle Database, portability between systems is guaranteed with up to 38 digits of precision.

The NUMBER column can contain the following numbers:

A positive number in the range 1 x 10 – 130 to 9.99 … 9x 10125

Oracle guarantees the portability of numbers with the precision of 38 digits or less. When specifying numeric fields, it is better to specify precision and scale.

MySQL Data type
BIG INT:NUMBER (19, 0)
INT:NUMBER(10,0)
MEDIUMINT:NUMBER(7, 0)
NUMERIC:NUMBER
SMALLINT:NUMBER(5,0)
TINYINT:NUMBER(3,0)
YEAR:NUMBER

primary keyのIDでnumberを使われていれば、intでいいでしょうね。

Database Object Name

1. General
Do not use capital letters
Neither table names nor column names use uppercase letters.
× DOCUMENTS
× Documents
〇 documents

2. Cooperation of multiple words is a snake case
Use snake case for both table name and column name. Camel case, camel back is NG.
× tableName
× TableName
× tablename
〇 table_name

3. English notation
Basically not in roman letters, but in English. Do not use abbreviations.

なるほどースネークケースか。
わかりましたぞー

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX \G

The INNODB_TRX table indicates whether the transaction is waiting for a lock, when the transaction starts, the SQL statement that the transaction is executing (if any), etc. Currently executing inside InnoDB (except for read-only transactions) contains information about all transactions.

TRX_ID
Unique transaction ID number within InnoDB. (As of Mysql5.6, these IDs are not created for read-only, non-locked transactions.)

TRX_WEIGHT
Transaction weight. This reflects the number of rows changed by the transaction and the number of rows locked(but not necessarily exactly). To resolve deadlocks, InnoDB chooses the transaction with the lowest weight as the target to roll back. Transactions that have changed non-transactional tables are considered more heavily weighted than other transactions.

TRX_STATE
Transaction execution status. One of Running, lock wait, rolling back, or commiting.

TRX_STARTED
Transaction start time.

TRX_REQUESTED_LOCK_ID
Lock ID on which the transaction is currently waiting (if TRX_STATE is lock wait, otherwise NULL). Details about locks can be found by linking to INNODB_LOCKS in LOCK_ID.

TRX_WAIT_STARTED
The time when the transaction started waiting for the lock(if TRX_STATE is lock wait, otherwise null).

TRX_MYSQL_THREAD_ID
Mysql thread id. It can be used to combine with Processlist by id.

TRX_QUERY
SQL query being executed by the transaction.

TRX_OPERATION_STATE
Transaction’s current operation or NULL.

TRX_TABLES_IN_USE
Number of InnoDB tables used when processing the current SQL statement for this transaction.

TRX_TABLES_LOCKED
The number of InnoDB tables for which the current SQL statement has a row lock.

TRX_LOCK_STRUTTS
Number of locks reserved in the transaction.

TRX_LOCK_MEMORY_BYTES
Total size used in memory by the lock structure for this transaction.

TRX_ROWS_LOCKED
Approximate number of rows locked by this transaction. This value may include rows that are marked for deletion that physically exist but are not recognized by the transaction.

TRX_ROWS_MODIFIED
Number of rows changed and inserted in this transaction.

TRX_CONCURRENCY_TICKETS
A value that indicates the amount of work that can be done in the current transaction before being swapped out, as specified by the innodb_concurrency_tickets option.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX \G
*************************** 1. row ***************************
                    trx_id: 3298
                 trx_state: RUNNING
               trx_started: 2014-11-19 13:54:39
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 316436
       trx_mysql_thread_id: 2
                 trx_query: DELETE FROM employees.salaries WHERE salary > 65000
       trx_operation_state: updating or deleting
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 1621
     trx_lock_memory_bytes: 243240
           trx_rows_locked: 759343
         trx_rows_modified: 314815
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0

うーん、なんか覚えておいた方が良さそうだなー、特にトラブル対応

でも、こーなる

mysql> select * from information_schema.innodb_trx\G;
Empty set (0.00 sec)

ERROR:
No query specified

INFORMATION_SCHEMA

Information schema provides access to db metadata and provides information about MySQL server such as database or table name, column data types, access privileges, and more. Another term that may be used for this information is the data dictionary and the system catalog.

Information schema is a database in each MySQL instance, where it stores information about all other databases held by MySQL server. The information schema database contains several read-only tables. Because they actually have views, there are no associated files, and you can not set triggers. Also, there is no database directory with that name.

Although you can select Information schema as the default database using the USE statement, you can only read the contents of table, not insert, update, or delete operations on the table.

mysql> select * from information_schema.innodb_trx\G;
Empty set (0.06 sec)

ERROR:
No query specified

mysql TRUNCATE

The TRUNCATE statement is prepared to delete all the data contained in the table. Use the following format.

TRUNCATE TABLE tbl_name;

Deletes all data contained in the specified table.
You can do the same with “DELETE FROM tbl_name” if you want to delete all data.

DELETE FROM tbl_name;

The deifferenct between the two is that “DELETE FROM tbl_name” deltes data one by one, while “TRUNCATE TABLE tbl_name” once creates “the same table” after “DROP TABLE tbl_name” is exected. It is often faster to delete and recreate the table itself rather than delete the data one by one.

If the table is of InnoDB type and is a parent table of external reference, executing “TRANCATE TABLE tbl_name” will delete data using “DELETE FROM tbl_name”. In this case, the ON DELETE clause is called in the child table. In the case of MyISAM type, the ON DELETE clause of the child table is not called because the data is deleted using delete table.

Let’s try sample script.
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+—————-+
| Tables_in_test |
+—————-+
| items |
| news |
+—————-+
2 rows in set (0.06 sec)

mysql> create table user(
-> id int auto_increment,
-> name varchar(20),
-> index(id));
Query OK, 0 rows affected (0.46 sec)

mysql> inser into user(name) values(‘flame’),(‘grilled’),(‘beef’),(‘crown’);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘inser into user(name) values(‘flame’),(‘grilled’),(‘beef’),(‘crown’)’ at line 1
mysql> insert into user(name) values(‘flame’),(‘grilled’),(‘beef’),(‘crown’);
Query OK, 4 rows affected (0.09 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from user;
+—-+———+
| id | name |
+—-+———+
| 1 | flame |
| 2 | grilled |
| 3 | beef |
| 4 | crown |
+—-+———+
4 rows in set (0.00 sec)

mysql> truncate table user;
Query OK, 0 rows affected (0.17 sec)

mysql> insert into user(name) values(‘standard’);
Query OK, 1 row affected (0.06 sec)

mysql> select * from user;
+—-+———-+
| id | name |
+—-+———-+
| 1 | standard |
+—-+———-+
1 row in set (0.00 sec)

ほう、
mysql> drop table user;
Query OK, 0 rows affected (0.11 sec)

mysql> create table user(
-> id int auto_increment,
-> name varchar(20),
-> index(id));
Query OK, 0 rows affected (0.14 sec)

mysql> insert into user(name) values(‘flame’),(‘grilled’),(‘beef’),(‘crown’);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> delete from user;
Query OK, 4 rows affected (0.00 sec)

mysql> inser into user(name) values(‘standard’);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘inser into user(name) values(‘standard’)’ at line 1
mysql> insert into user(name) values(‘standard’);
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+—-+———-+
| id | name |
+—-+———-+
| 5 | standard |
+—-+———-+
1 row in set (0.00 sec)

おおおおおおおおおおおおおおおおおおおお、すげえええええええええええええええええええええええええええええええ

MySQL “show processlist”

show proccesslist
It is a command to view the list of currently running processes. Example, it confirm that the batch is moving, or when get a heavy query.

Tha SQL that is being executed will be cut off in the middle of a line for info, and it should be displayed roughly.

mysql> show processlist;
+—-+——+———–+——+———+——+——-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——+———–+——+———+——+——-+——————+
| 4 | root | localhost | NULL | Query | 0 | init | show processlist |
+—-+——+———–+——+———+——+——-+——————+
1 row in set (0.05 sec)

difference in autocommit valid and invalid

Depending on whether “autocommit” is enabled or disabled, the transaction start / end method changes.

When auto commit is enabled(It is enabled by default)
The transaction in this case is started implicitly at the execution of single SQL statement or explicitly by the START TRANSACTION statement.

The behavior changes depending on whether you execute the START TRANSACTION statement or not in this.Generally, there are the following differences.

1. When START TRANSACTION statement is executed
It does not commit when a single SQL statement is executed, but commits only when a COMMIT statement is executed. Roll back if you execute the ROLLBACK statement.

2. If you did not execute the START TRANSACTION statement
Commit automatically when a single SQL statement is executed. You can not roll back.

Case 2 is similar to the operation is MyISAM table which is always committed immediately.

If you disable autocommit
The transaction in this case is started implicitly by the execution of the SQL statement. Or, explicitly start by executing the START TRANSACTION statement. In either case, it does not commit automatically, but is committed only when the COMMIT statement is executed. Also, roll back if you execute the ROLLBACK statement.

The meaning of “a transaction starts implicitly by the execution of a SQL statement” means that a transaction is automatically started even if you forget to execute a START TRANSACTION statement.

Disabling auto-commit always assumes that the user has a transaction open. Therefore, the user explicitly COMMITs the behavior that the current transaction is ended and then a new transaction is started.

SELECT @@autocommit;
[vagrant@localhost ~]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.41 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> SELECT @@autocommit;
+————–+
| @@autocommit |
+————–+
| 1 |
+————–+
1 row in set (0.06 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.05 sec)

mysql> select @@autocommit;
+————–+
| @@autocommit |
+————–+
| 0 |
+————–+
1 row in set (0.00 sec)

MySQL transaction

A transaction is a unit of work or unit of work performed in a DB system.

The result of the transaction is either “commit” or “rollback”.

■ Commit
Commit is a process that is executed when there is no problem with all the processes in the transaction. Execution will confirm the processing, and the result of the transaction will be reflected in the DB. You can not roll back committed data.

■ Rollback
Rollback is a process that is executed when a problem occurs in the processing of a transaction. When executed, data is returned until the transaction start time. Rollback is not process(SQL) unit in a transaction. It is done on a transaction basis.

MySQL auto commit

One of MySQL functions, autocommit is enabled / disabled and how it changes behavior.

Bottom line that InnoDB table is transaction safe.
(MySAM does not support transactions)

あれ、そもそもトランザクションって何?単語から何かの処理なんだろうなーってことはわかるけど???

DB auto commit and manual commit

There are two methods of transaction commit: auto commit and manual commit.

Auto-commit means that automatically starts and terminates a transaction for each data operation, that is, for each API call.
Manual commit means that the application commits by executing commit.

Use manual commit to combine multiple update operations into a single transaction.

When establishing a connection, auto-commit is enabled. If auto-commit is enabled, it will be auto-commit, and if auto-commit is disabled, it will be manual commit. Switching between auto commit and manual commit is configured with API.