Table

You can create tables in the database. Table specifications are MySQL 8.0 compatible. The database is created on a GUI basis.

Table Creation

Open the tree of the database for which you want to create tables and right-click on "Tables" to open the context menu. Select "Context Menu" - "Create Table" - "Normal Table". An input screen for creating a new table appears in the right pane.入力スクLean has basic information at the top, and below that, the Columns tab, Index tab, Foreign Keys tab, and Triggers tab. Make the necessary entries, click the "Apply" button, and follow the instructions to create the table. Basic information will include the following:

Setting	Description
Table name	Enter a table name
Character set	Enter the character set for the table. Defaults to the database setting. Cannot be changed.
Collation	Enter the collation for the table. Defaults to the database setting. Cannot be changed.
Storage engine	It is InnoDB. Cannot be changed.

Column

After selecting the "Columns" tab, register the column information that makes up the table. Repeat the following steps.

Action	Description
Add column	1. Click the [+] button. Follow the entry instructions. 2. After column addition is complete, click the "Apply" button.
Delete column	Check the columns you wish to delete and click the Delete button.

Input Description

Setting	Description
Column name	Enter a column name.
Data type	Enter data type for the column. Depending on the selection of data type, the constraints and digit fields that can be set change from active to inactive.
M	- DECIMAL type: Sum of integer and decimal digits (1 to 65). If omitted, 10 is handled. - CHAR type: Number of characters that can be stored (0 to 255). If omitted, treated as 1. - VARCHAR type: Number of characters that can be stored (0 to 65,535). Cannot be omitted. - BINARY type: Number of bytes that can be stored (0 to 255). If omitted, treated as 1. - VARBINARY type: Number of bytes that can be stored (0 to 65,535). Cannot be omitted.
D	- DECIMAL type: Number of decimal places (0 to 30). If omitted, treated as 0.
PK	PRIMARY KEY Constraint
NN	NOT NULL Constraint
UQ	UNIQUE Constraint
UN	UNSIGNED Constraint
AI	AUTO_INCREMENT attribute

Tips

Tips

Columns with a PRIMARY KEY constraint are automatically assigned a PRIMARY index. Columns with UNIQUE KEY constraints are automatically assigned UNIQUE indexes. The AUTO_INCREMENT attribute can be assigned only to the first column.

Data type

Number types

Data type	Range (signed)	Range (when unsigned UN is specified)
TINYINT	-128～127	0～255
SMALLINT	-32768～32767	0～65535
MEDIUMINT	-8388608～8388607	0～16777215
MINT	-2147483648～2147483647	0～4294967295
BIGINT	-9223372036854775808～9223372036854775807	0～18446744073709551615

Floating point type

Data type	Range
FLOAT	-3.402823466E+38～-1.175494351E-38, 0, 1.175494351E-38～3.402823466E+38
DOUBLE	-1.7976931348623157E+308～-2.2250738585072014-308, 0,

Fixed-point type

Data type	Range
DECIMAL	M is the sum of integer and decimal digits (1 to 65) If omitted, it is treated as 10 D is the number of decimal places (0 to 30) If omitted, it is treated as 0.

Date type and time type

Data type	Format	Range
DATE	’YYYY-MM-DD'	'1000-01-01’～‘9999-12-31’
DATETIME	’YYYY-MM-DD HH:MM:SS'	'1000-01-01 00:00:00’～‘9999-12-31 23:59:59’
DATETIME(fsp)	‘YYYY-MM-DD HH:MM:SS.fraction'	'1000-01-01 00:00:00.000000’～‘9999-12-31 23:59:59.999999’
TIMESTAMP	’YYYY-MM-DD HH:MM:SS'	'1970-01-01 00:00:00’ UTC ～ ‘2038-01-19 03:14:07’ UTC
TIMESTAMP(fsp)	‘YYYY-MM-DD HH:MM:SS.fraction'	'1970-01-01 00:00:00.000000’ UTC ～ ‘2038-01-19 03:14:07.999999’ UTC
TIME	’HH:MM:SS'	'-838:59:59’ ～ ‘838:59:59’
TIME(fsp)	‘HH:MM:SS.fraction'	'-838:59:59.000000’ ～ ‘838:59:59.000000’

Tips

Tips

• For DATETIME and TIMESTAMP types, null may be assigned when NOTNULL constraints are attached.
• In the case of the DATE type, “1899-11-30” may be assigned when a NOTNULL constraint is attached.

String type

Data type	Characteristic	Range
CHAR	fixed length string	M is the number of characters that can be stored (0 to 255).
VARCHAR	variable length string	M is the number of characters that can be stored (0 to 65,535) Cannot be omitted

Binary string type

Data type	Characteristic	Range
BINARY	fixed length binary string	M is the number of bytes that can be stored (0 to 255).
VARBINARY	variable length binary string	M is the number of bytes (0-255) that can be stored.

BLOB type

Data type	Characteristic	Maximum length (bytes)
TINYBLOB	variable length binary string	255
BLOB	variable length binary string	65,535
MEDIUMBLOB	variable length binary string	16,777,215
LONGBLOB	variable length binary string	4,294,967,295

TEXT type

Data type	Characteristic	Maximum length (bytes)
TINYTEXT	variable length character string	255
TEXT	variable length character string	65,535
MEDIUMTEXT	variable length character string	16,777,215
LONGTEXT	variable length character string	4,294,967,295

Index

Select the "Index" tab to display the index list. Repeat the following steps:

Action	Description
Add Index	1. Click [+] button. 2. Enter the index name and index type. 3. With the row with the index name selected (highlighted), check the columns to which you want to apply the index from the right side and click the "Apply" button.
Delete Index	1. Check the row with the index name you wish to delete and click the Delete button. 2. After a row is deleted from the index name list, click the "Apply" button.

Input Description

Setting	Description
Index Name	Enter an index name.
Index Type	Enter the index type.
Index Column	Specify by check which columns to add indexes to. If multiple checks are made, the index will be a composite index.
Combined index number (#)	Specifies the composite index number.
Index sequence	Specifies ascending (ASC)/descending (DESC) order of indexes.

Index Type

The index types that can be added are as follows

Index Type	Description
INDEX	General Index
UNIQUE	Index with unique constraints
FULLTEXT	Index for full text search
PRIMARY	Index of primary key

Tips

Tips

Only CHAR, VARCHAR, TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT types can be given the FULLTEXT type.

Tips

Tips

• Indexes of type PRIMARY are automatically assigned by assigning a PRIMARY KEY constraint to the column. It is not possible to grant an index from an index.
• When a foreign key is set to a column, an index of type INDEX is automatically assigned.

Specifying an index length allows indexes to be created on only the specified length of column values. The meaning of length depends on the data type. BLOB and TEXT types must be specified.

Data type	Meaning of Length	Required
CHAR type	number of characters
VARCHAR type	number of characters
BINARY type	number of bytes
VARBINARY type	number of bytes
TINYBLOB type	number of bytes	required
BLOB type	number of bytes	required
MEDIUMBLOB type	number of bytes	required
LONGBLOB type	number of bytes	required
TINYTEXT type	number of characters	required
TEXT type	number of characters	required
MEDIUMTEXT type	number of characters	required
LONGTEXT type	number of characters	required

Foreign key

Select the Foreign Keys tab to display the foreign key list. Repeat the following operations.

Action	Description
Add foreign key	1. Click [+] button. 2. Enter the foreign key name and referenced table information. 3. From the column list on the right, select the column for which you want to set a foreign key and the column to which you want to refer. 4. Set the foreign key option. 5. Click the "Apply" button to confirm.
Delete foreign key	1. Check the row for the foreign key name you wish to delete and click the Delete button. 2. After the row is deleted from the foreign key list, click the "Apply" button.

Input Description

Setting	Description
Foreign key name	Enter a foreign key name.
Reference table name	Specifies the table to which the foreign key refers.
Foreign key column	Specify the column of the table for which the foreign key is to be set.
Reference column	Specifies the column that the foreign key refers to.
Action on foreign key update	Select the foreign key option. The default is NO ACTION.
Action on foreign key deletion	Select the foreign key option. The default is NO ACTION.

Tips

Tips

• The data type of the column to which the foreign key is set must match the data type of the referenced column.
• The referencing column of the foreign key must have an index set.

Foreign Key Option

Action	time of update	time of deletion
RESTRICT	If the reference is updated, an error	If the reference is deleted, an error
CASCADE	Updated when references are updated	Deleted when the reference is deleted
SET NULL	Replaced by NULL when the reference is updated	Replaced by NULL when the reference is deleted
NO ACTION	Same as RESTRICT	Same as RESTRICT

Table Update

1. After the table is created, select the table from the tree.
2. Change the table settings and click the "Apply" button.
3. The changed part will be updated.

Table Deletion

1. Right-click on the table you wish to delete and click "Context Menu" – "Delete Table".
2. The table will be deleted.

Tips

Tips

Once a table is deleted, it cannot be recovered. It is recommended to export the table data as required.

Data Display

Selecting the "Data" tab will display the data in the table (up to 1,000 records) in a table. You can use the "Column Filter" to narrow down the data.

Tips

Tips

• If you use the column filter, the filter will be narrowed down from the 1000 displayed.
• To check and manipulate data after 1001 cases, use the SQL editor However, the SQL editor is a feature of the Premium execution environment only. In the free execution environment, data after 1001 cannot be referenced from the development editor.

Add Record

After the data is displayed, records can be added by the following operation.

1. Click the [+] button.
2. Enter a record on a new line.
3. Click the "Commit" button.

Update Record

After displaying data, records can be updated by the following operation.

1. Click the [+] button.
2. Click on each cell in the row you want to update to change the record contents.
3. Click the "Commit" button.

Delete Record

After the data is displayed, records can be deleted by the following operation.

1. Check the beginning of the record you want to delete.
2. Click the Delete icon to remove a record from the table.
3. Click the "Commit" button.

Export

Data can be exported on a table-by-table basis. When exporting, there are two export types "File" and "Folder". Export type "File" is the same functionality as in V1.3.3 and earlier exports. Use "folder" if any of the following apply

• If the table contains binary data
• When export data is several tens of MB

<When using export type "Folder">

1. Click on the "…"to the right of the table you wish to export and select "Export".
2. Select "Folder".
3. Select the folder path to export to and click the "Export" button.
4. The export job is started. The status of jobs can be checked on the Service Request screen.
5. Exported to the target path with the following folder structure

   <target path>/
     - <base folder>/
       - rows/

   For information on each folder, output files, and file formats, see the Exporting Databases page.

Tips

Tips

• Do not perform INSERT or other operations on the exported table when executing the export.
• Exported data will not be automatically deleted if you cancel or exit in error during the process.
• When exporting, the following will be output to the Event Log will output the following
  • [yyyy-mm-dd hh:mm:ss.sss] [Database] [Email] Export job requested - Job ID: <job ID>, Database: <database name>, Table:<table name>, Data

<When using export type "File>

1. Click on the "Vertical Triple Point Reader"to the right of the table you wish to export and select "Export".
2. Select "File".
3. The CSV file is downloaded locally.

Tips

Tips

• Do not perform any INSERT or other operations on the exported table while the export is in progress.
• Output binary data is not supported.
• The export request takes 29 seconds and a timeout error is displayed.
• Errors may occur with tens of MB of data to be exported.

Import

Data can be imported into the table using the file output by the export function described above.

<For data exported with export type "Folder">

1. Click on the "…" to the right of the table you wish to import and select "Import".
2. Select "Folders". Select the exported base folder and the exported data and press the "Import" button.
3. The import job is started. The status of jobs can be checked on the Service Request screen.
4. The contents of the specified folder will be imported into the table.

Tips

Tips

• Do not perform INSERT or other operations on the table to be imported during the import run.
• If the unique key value is the same for an input row and an existing row in the table, it is replaced.
• Imported data will not be automatically deleted if you cancel or exit with an error during the process.
• if importing is performed, the following will be output to Event Log will output the following
  • [yyyy-mm-dd hh:mm:ss.sss] [Database] [Email] Import job requested - Job ID: <job ID>, Database: <database name>, Table:<table name>

<For data exported with export type "File" >

1. Click on the "…" to the right of the table you wish to import and select "Import".
2. Select "File".
3. The contents of the specified folder will be imported into the table.

The CSV file should follow the following format

Setting	Description
File encoding	UTF-8
1 record	one row
Escape character	\ (backslash)
Newline character	CRLF or LF
Delimiter	, (comma)
Enclosing letter	” (double quotation)
Header	Excluded

CSV data example

"P_001","2020-09-21","P_001",140.0,4.0,8.0
"P_002","2020-09-14","P_002",100.0,1.0,8.0
"P_003","2020-09-08","P_003",120.0,3.0,8.0
：

Tips

Tips

• CSV files exported with export type "File" have a header line added to the first line. Remove header lines when using in import.
• Binary data exported with export type "File" is not supported. Binary data should be deleted when used in import.
• Do not perform INSERT or other operations on the table to be imported during the import run.
• The import request takes 29 seconds and a timeout error is displayed
• The import process itself will time out in 15 minutes and terminate with an error.
• Errors may occur with tens of MB of data to be imported.
• Files output with export type "File" will not be able to be imported in the future.
• Please use the export type "Folder" and output the file again.

ER Diagram

ER diagrams can be output as a physical model of the database based on information extracted from the database. For more information on ER diagrams, please refer to the ER diagram.