Ethereum: Why can there be several addresses in the transaction output?

Understanding Ethereum Transaction Outputs and Multiple Addresses

When trying to load your Ethereum blockchain into a MySQL database, it is important to understand how Ethereum transactions are structured, especially when it comes to output values. In this article, we will explore why Ethereum allows multiple addresses in a transaction output and explore the implications for data storage.

Transaction Outputs

Ethereum transactions typically have one input (also known as the “from” address) and one or more outputs (also known as the “to” address). Each output value is associated with a unique address, which can be a private key or a public address. When you send funds from an input to multiple outputs, each output value receives a corresponding amount of Ether.

Problem: Multiple Addresses in a Single Transaction

In Ethereum, there are cases where it is possible to have multiple addresses within the same transaction output. This is known as “multiple addresses” or “nested addresses”. To address this, the Ethereum protocol uses a mechanism called “address encoding”, which allows multiple addresses to be stored together.

When you create a new Ethereum account, your public address is an example of such encoding. If you have multiple accounts with different public addresses (e.g. 0x... y 0x...), both can exist in the same transaction output, even though they are separate inputs.

MySQL Database Considerations

To load your Ethereum blockchain into a MySQL database, you will need to convert the raw transaction data from Ethereum’s JSON-LD format (the standard for representing Ethereum transactions) into a more manageable format. In doing so, you may encounter issues with storing and querying multiple addresses in a single output.

Multiple Addresses

In MySQL, table columns can only have a certain number of values; each value must be distinct from the others. However, when you store the outputs of an Ethereum transaction with multiple addresses (e.g. 0x..., 0x..., and 0x...), you are essentially creating a single column that stores three separate sets of addresses.

To get around this limitation, MySQL supports “table variables” or “temporary tables”, which allow you to store values ​​​​in a single column with multiple rows. You can create a temporary table using the CREATE TABLE command and populate it with your Ethereum transaction data:

-- Create a temporary table to store Ethereum output addresses
CREATE TEMPORARY TABLE ethereum_outputs (
address VARCHAR(42), -- Assuming a 42-character limit per address
value DECIMAL(8, 5) // Stores Ether amounts in decimal format
);
-- Insert Ethereum transaction data into a temporary table
INSERT INTO ethereum_outputs (address, value)
SELECT 
'0x...', 
'0x...',
'0x...'
FROM your_transaction_data;
-- Query a temporary table to access multiple addresses in a single output
SELECT address, value FROMethereum_outputs WHERE address IN ('0x...', '0x...');

Note that this approach assumes that you are using MySQL version 8.0 or later, which supports CREATE TEMPORARY TABLE and the IN query clause.

Conclusion

In short, Ethereum allows multiple addresses within the output of a single transaction due to address encoding. To load your Ethereum blockchain into a MySQL database, you can create temporary tables with separate columns for each address in the transaction output. By using table variables or stored procedures, you can efficiently store and query this data while avoiding column addressing limitations.

I hope this article helped clarify the concept of multiple addresses in Ethereum transaction output and provided guidance on how to work around MySQL’s column addressing limitations.